In chapter 10, we expanded the scope of our thinking a bit. We saw how to use SQL not only to query tables but also, with the help of set operators such as UNION or INTERSECT, to combine the results of two or more SELECT statements to form a single result set. In this chapter, we’ll build on that knowledge by examining an important method of evaluating the results of multiple SELECT statements in the same query: the subquery.
Subqueries are simply queries nested into another query. We use subqueries when we can’t achieve the desired results from a single SELECT statement, so instead of writing two or more queries, we combine them into a single query. Don’t worry—this process isn’t as complicated as it sounds.
By the end of this chapter, you’ll see how subqueries allow you to evaluate the results of SELECT statements in ways beyond the capabilities of the set operators you learned about in preceding chapters. We have a lot of ways to use subqueries to discover, so let’s get started.
11.1 A simple subquery
As I’ve noted throughout the book, a SELECT statement is a type of SQL query that returns a set of data known as a result set. So far, you’ve executed dozens of queries that produce result sets. You’ve queried one or more tables, producing result sets that look a lot like tables. By that, I mean the results have rows and columns, and the columns have names.