Microsoft SQL Server 2012 Bible

Subqueries

A subquery is an embedded SQL statement within an outer query. The subquery provides an answer to the outer query in the form of a scalar value, a list of values, or a data set and may be substituted for an expression, list, or table, respectively, within the outer query. Traditionally, a subquery may contain only a SELECT query and not a data-modification query, which explains why subqueries are sometimes referred to as subselects.

Three basic forms are possible when building a subquery, depending on the data needs and your favored syntax:

• Simple subquery: The simple subquery can be a standalone query and can run by itself. It is executed once, with the result passed to the outer query. Simple subqueries are constructed as normal SELECT queries and placed within parentheses.
• Common table expression (CTE): CTEs are a syntactical variation of the simple subquery, similar to a view, which defines the subquery at the beginning of the query using the WITH command. The CTE can then be accessed multiple times within the main query as if it were a view or derived table.
• Correlated subquery: This is similar to a simple subquery except that it references at least one column in the outer query, so it cannot run independently. Conceptually, the outer query runs first, and the correlated subquery runs once for every row in the outer query. Physically, the Query Optimizer might generate a more efficient plan.

Simple Subqueries

Simple subqueries are executed in the following order: ...