SQL Server™ 2005 Bible

Chapter 10. Including Data with Subqueries and CTEs

In This Chapter

• Understanding subquery types

• Building simple and correlated subqueries

• Using common table expressions (CTEs)

• Solving problems with relational division

SQL's real power is reflected by the capability it offers 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 done with the 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 select 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 10-1. Because a subquery may only contain a select query, and not a data-modification query, 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 stand-alone query and can run by itself. It is executed once, with the result passed to the outer query. Simple ...