Subqueries, also called inner queries, appear within a where or having clause of another SQL statement, or in the select list of a statement. You can use subqueries to handle query requests that are expressed as the results of other queries. A statement that includes a subquery operates on rows from one table, based on its evaluation of the subquery’s select list, which can refer either to the same table as the outer query, or to a different table. In Transact-SQL, you can use a subquery almost anywhere an expression is allowed, if the subquery returns a single value. A case expression can also include a subquery.
For example, this subquery lists the names of all authors whose royalty split is more than $75:
select au_fname, au_lname from authors where au_id in (select au_id from titleauthor where royaltyper > 75)
select statements that contain one or more subqueries are sometimes called nested queries or nested select statements.
The result of a subquery that returns no values is NULL. If a subquery returns NULL, the query failed.
See the Reference Manual: Commands.
