Learning SQL

Chapter 9. Subqueries

Subqueries are a powerful tool that can be used in all four SQL data statements. This chapter will explore in great detail the many uses of the subquery.

What Is a Subquery?

A subquery is a query contained within another SQL statement (which I will 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 table that may consist of:

• A single row with a single column

• Multiple rows with a single column

• Multiple rows and columns

The type of table returned by the subquery determines how it may be used and which operators may be used by the containing statement to interact with the table returned by the subquery. When the containing statement has finished executing, the tables returned by any subqueries are 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 have already seen several examples of subqueries in earlier chapters, but here’s a simple example to get started:

    mysql> SELECT account_id, product_cd, cust_id, avail_balance
        -> FROM account
        -> WHERE account_id = (SELECT MAX(account_id) FROM account); +------------+------------+---------+---------------+ | account_id | product_cd | cust_id | avail_balance | +------------+------------+---------+---------------+ ...