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’s WHERE clause, or where a comparison operator is modified by the ALL, ANY, or SOME 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’s WHERE clause.

Subqueries can be nested within other subqueries as search conditions, establishing a chain of parent/child queries.

Topics

• Simple Subqueries
• Correlated Subqueries