Subqueries
Subqueries are select statements nested in other select statements. For example:
select ename
from employee
where dept in
(select dno
from dept
where floor = 3);
Use subqueries in a where clause to qualify a specified column against a set of rows. In the previous example, the subquery returns the department numbers for departments on the third floor. The outer query then retrieves the names of employees who work in those departments.
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 earlier in this chapter. The select clause of a subquery must contain only one element.
A subquery can refer to 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, or it would be implicitly qualified by empy. The query is evaluated by assigning empx each of its values (that is, letting it range over the employee table), and evaluating the subquery for each value of empx. At least one of the correlation names must be specified in this example-either empx or empy, but not both, can be allowed to default to employee.
For more information about using correlation names in nested subqueries, see Correlation Names in the chapter “OpenSQL Data Types.”