Learning SQL, 3rd Edition

Chapter 9. Subqueries

Subqueries are a powerful tool that you can use in all four SQL data statements. In this chapter, I’ll show you how subqueries can be used to filter data, generate values, and construct temporary data sets. After a little experimentation, I think you’ll agree that subqueries are one of the most powerful features of the SQL language.

What Is a Subquery?

A subquery is a query contained within another SQL statement (which I refer to as the containing statement for the rest of this discussion). A subquery is always enclosed within parentheses, and it is usually executed prior to the containing statement. Like any query, a subquery returns a result set that may consist of:

• A single row with a single column

• Multiple rows with a single column

• Multiple rows having multiple columns

The type of result set returned by the subquery determines how it may be used and which operators the containing statement may use to interact with the data the subquery returns. When the containing statement has finished executing, the data returned by any subqueries is discarded, making a subquery act like a temporary table with statement scope (meaning that the server frees up any memory allocated to the subquery results after the SQL statement has finished execution).

You already saw several examples of subqueries in earlier chapters, but here’s a simple example to get started:

 mysql> SELECT customer_id, first_name, last_name     -> FROM customer     -> WHERE customer_id ...