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.