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.
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. ...