Subqueries

You can nest a SELECT statement inside a SELECT, INSERT, UPDATE, or DELETE statement, another subquery, or anywhere an expression is allowed. However, the expression that includes the subquery must return true or false. Usually a subquery is nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, DELETE, or another subquery. Many SQL statements that include subqueries can also be formulated as joins.

Syntax

A subquery can be used in these contexts:

expression comparison_operator [ANY | ALL | SOME] (subquery)

expression [NOT] IN (subquery)

[NOT] EXISTS (subquery)

A subquery has the following restricted SELECT syntax:

(SELECT [ALL | DISTINCT] subquery_select_list
[FROM {table_name | view_name}[optimizer_hints]
    [[, {table_name2 | view_name2}[optimizer_hints]
    [..., {table_name16 | view_name16}[optimizer_hints]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause])

where

expression

Is a standard expression. For details, see the Expressions and Search Conditions topics.

comparison_operator

Is a standard comparison operator. For details, see the Operators topic.

ANY | SOME

Are synonymous. When used, the expression evaluates to true if the comparison is true for at least one row of subquery. The expression evaluates to false if the comparison returns no rows or if the comparison is false for all rows in the subquery. Otherwise, the expression is unknown.

ALL

When used the expression evaluates to true if the comparison returns no rows or if the comparison is true for all rows in the subquery. The expression evaluates to false if the comparison is false for at least one of the rows in the subquery. Otherwise, the expression is unknown.

DISTINCT

Includes only unique rows in the results. Cannot be used with subqueries that include a GROUP BY clause.

subquery_select_list

Is a restricted select list for use with subqueries introduced with IN or a comparison operator. It consists of one expression. If a column name is used in the WHERE clause of the outer statement, a column name in the subquery select list must be join-compatible with it.

The select list must consist of only one column name except for the EXISTS subquery, in which case an asterisk (*) is usually used in place of the single column name. Do not specify more than one column name or a column of text and image datatype; these are not allowed in subqueries.

Examples

A.    Simple Subqueries

Note that the syntax for EXISTS is somewhat different from the syntax for the other keywords; it does not take an expression between WHERE and itself. In the following examples, the queries, which are semantically equivalent, illustrate the difference.

SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
    (SELECT *
    FROM titles
    WHERE pub_id = publishers.pub_id
    AND type = 'business')

Or

SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
    (SELECT pub_id
    FROM titles
    WHERE type = 'business')

B.    Correlated Subquery

A correlated (or repeating) subquery depends on the outer query for its values. It is executed repeatedly, once for each row that might be selected by the outer query. Here is an example:

SELECT DISTINCT au_lname, au_fname
FROM authors
WHERE 100 IN
    (SELECT royaltyper
    FROM titleauthor
    WHERE titleauthor.au_id = authors.au_id)

The subquery in this statement cannot be evaluated independently of the outer query. It needs a value for authors.au_id, but this value changes as SQL Server examines different rows in authors.

C.    Correlated Subquery with GROUP BY and HAVING Greater Than ALL

A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the types of books for which the maximum advance is more than twice the average for the group.

SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >= ALL
    (SELECT 2 * AVG(t2.advance)
    FROM titles t2
    WHERE t1.type = t2.type)

D.    Multiple Correlated Subqueries

This example finds the names of authors who have participated in writing at least one popular computing book.

SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
    (SELECT au_id
    FROM titleauthor
    WHERE title_id IN
        (SELECT title_id
        FROM titles
        WHERE type = 'popular_comp'))

See Also