Using Subqueries (Embedded SQL Guides)
Go Up to Working with Data
A subquery is a parenthetical SELECT
statement nested inside the WHERE
clause of another SELECT
statement, where it functions as a search condition to restrict the number of rows returned by the outer, or parent, query. A subquery can refer to the same table or tables as its parent query, or to other tables.
The elementary syntax for a subquery is:
SELECT [DISTINCT] col [, col ...] FROM <tableref> [, <tableref> ...] WHERE {expression {[NOT] IN | comparison_operator} | [NOT] EXISTS} (SELECT [DISTINCT] col [, col ...] FROM <tableref> [, <tableref> ...] WHERE <search_condition>);
Because a subquery is a search condition, it is usually evaluated before its parent query, which then uses the result to determine whether or not a row qualifies for retrieval. The only exception is the correlated subquery, where the parent query provides values for the subquery to evaluate. For more information about correlated subqueries, see Correlated Subqueries.
A subquery determines the search condition for a parent’s WHERE
clause in one of the following ways:
- Produces a list of values for evaluation by an
IN
operator in the parent query’sWHERE
clause, or where a comparison operator is modified by theALL
,ANY
, orSOME
operators. - Returns a single value for use with a comparison operator.
- Tests whether or not data meets conditions specified by an
EXISTS
operator in the parent query’sWHERE
clause.
Subqueries can be nested within other subqueries as search conditions, establishing a chain of parent/child queries.