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.
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
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.
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')
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.
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)
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'))
DELETE | INSERT |
EXECUTE | SELECT |
Expressions | UPDATE |
Functions |