A subquery nested in the outer SELECT statement has the following 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])
The SELECT statement of a subquery is always enclosed in parentheses. It cannot include an ORDER BY, COMPUTE, or FOR BROWSE clause.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. There is no limit on the level of nesting. A subquery can appear anywhere an expression can be used, as long as it returns a single value. You cannot use a subquery in an ORDER BY list.
If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).
Statements that include a subquery usually take one of these formats:
WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL] (subquery)
WHERE [NOT] EXISTS (subquery)
In some SQL statements, the subquery (also called the inner query) can be evaluated as if it were an independent query. Conceptually, the results of the subquery are substituted into the main statement or outer query (although this is not how SQL Server actually processes SQL statements with subqueries).
There are three basic types of subqueries: