WHERE

The WHERE function specifies a result table that consists of those rows of R for which the search condition is true. R is the result of the FROM clause of the subselect.

The search condition must conform to the following rules:

• Each column name must unambiguously identify a column of R or be a correlated reference. A column name is a correlated reference if it identifies a column of a table, view, common-table-expression, or nested-table-expression that is identified in an outer subselect.
• An aggregate function must not be specified unless the WHERE clause is specified in a subquery of a HAVING clause and the argument of the function is a correlated reference to a group.

Any subquery in the search-condition is effectively executed for each row of R and the results are used in the application of the search-condition to the given row of R. A subquery is actually executed for each row of R only if it includes a correlated reference. In fact, a subquery with no correlated references is executed just one time, whereas a subquery with a correlated reference might have to be executed one time for each row.

The column access control does not affect the operation of the WHERE clause.

Example:

The following example returns the rows from the table EMPLOYEE with columns EMPL_NAME and AGE where AGE is greater than 50.

SELECT EMPL_NAME,AGE FROM EMPLOYEE  
WHERE AGE >50

The above example returns the following:

BUMRAH , 53
NEWTON , 53