Subselects
Subselects (also known as subqueries) are SELECT statements placed in a WHERE or HAVING clause. The results returned by the subselect are used to evaluate the conditions specified in the WHERE or HAVING clause.
Subselects must return a single column, and cannot include an ORDER BY or UNION clause.
The following example uses a subselect to display all employees whose salary is above the average salary:
SELECT * FROM employees WHERE salary >
(SELECT avg(salary) FROM employees);
In the preceding example, the subselect returns a single value: the average salary. Subselects can also return sets of values. For example, the following query returns all employees in all departments managed by Barth.
SELECT ename FROM employees WHERE edept IN
(SELECT ddept FROM departments
WHERE dmgr = 'Barth');
Note: For details about the operators used in conjunction with subselects, see the chapter “Understanding the Elements of SQL Statements.”