Microsoft® SQL Server® 2012 Bible

237

Chapter 9: Merging Data with Joins, Subqueries, and CTEs

9

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-modifi cation

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 subque-
ries 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 defi nes 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 refer-
ences at least one column in the outer query, so it cannot run independently.
Conceptually, the outer query runs fi rst, and the correlated subquery runs once
for every row in the outer query. Physically, the Query Optimizer might generate a
more effi cient plan.
Simple Subqueries
Simple subqueries are executed in the following order:

1. The simple subquery is executed once.


2. The results are passed to the outer query.


3. The outer query is executed once.

The most basic simple subquery returns a single (scalar) value, which is then used as an

expression in the outer query, as follows:

SELECT (SELECT 3) AS SubqueryValue;

Result:

SubqueryValue

--------------

3

c09.indd 237c09.indd 237 7/30/2012 4:25:10 PM7/30/2012 4:25:10 PM

http://www.it-ebooks.info