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, a subquery can also be used 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.
You can formulate as joins many SQL statements that include a subquery. Other questions can be posed only with subqueries. Some people find subqueries easier to understand. Other SQL users avoid subqueries whenever possible. You can choose whichever formulation you prefer.
The result of a subquery that returns no values is NULL. If a subquery returns NULL, the query failed.
See the Reference Manual: Commands.
