Microsoft® SQL Server® 2008 Bible

Chapter 11. Including Data with Subqueries and CTEs

IN THIS CHAPTER

• Understanding subquery types

• Building simple and correlated subqueries

• Fitting subqueries in the query puzzle

• Using common table expressions (CTEs)

• Solving problems with relational division

• Passing data with composable SQL

SQL's real power is its capability to mix and match multiple methods of selecting data. It's this skill in fluidly assembling a complex query in code to accomplish what can't be easily done with GUI tools that differentiates SQL gurus from the wannabes. So, without hesitation I invite you to study embedded simple and correlated subqueries, derived tables, and common table expressions, and then apply these query components to solve complex relational problems such as relational division.

Methods and Locations

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. The matrix of subquery types and SELECT statement usage is shown in Table 11-1. Traditionally, a subquery may only contain a SELECT query and not a data-modification query, which explains why subqueries are sometimes referred to as subselects.

Five 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 stand-alone query and can run by itself. ...