Language Reference Guide : Language Elements : Subqueries : Subqueries in the WHERE Clause
 
Share this page          
Subqueries in the WHERE Clause
A subquery in a WHERE clause can be used to qualify a column against a set of rows.
For example, the following subquery returns the department numbers for departments on the third floor. The outer query retrieves the names of employees who work on the third floor.
SELECT ename
FROM employee
WHERE dept IN
       (SELECT dno
       FROM dept
       WHERE floor = 3);
Subqueries often take the place of expressions in predicates. Subqueries can be used in place of expressions only in the specific instances outlined in the descriptions of Predicates in SQL.
The syntax of the subquery is identical to that of the subselect, except the SELECT clause must contain only one element. A subquery can see correlation names defined (explicitly or implicitly) outside the subquery. For example:
SELECT ename
FROM employee empx
WHERE salary >
       (SELECT AVG(salary)
       FROM employee empy
       WHERE empy.dept = empx.dept);
The preceding subquery uses a correlation name (empx) defined in the outer query. The reference, empx.dept, must be explicitly qualified here. Otherwise the dept column is assumed to be implicitly qualified by empy. The overall query is evaluated by assigning empx each of its values from the employee table and evaluating the subquery for each value of empx.
Note:  Although aggregate functions cannot appear directly in a WHERE clause, they can appear in the SELECT clause or the HAVING clause of a subselect, which itself appears in a WHERE clause.