13 Working with Subqueries

13.1 Understanding Subqueries

SELECT statements are SQL queries. All of the SELECT statements you have seen thus far in this book are simple queries — single statements retrieving data from an individual database table (or, in a few cases, using a WHERE clause with a hard-coded list of values).

SQL also enables you to create subqueries — queries that are embedded into other queries. Why would you want to do this? The best way to understand this concept is to look at a couple of examples.

13.2 Filtering by Subquery

The museum database used throughout this book is a relational database. Works of art are stored in the work table. Each work is linked to the artist who created it (through artist_id) and the museum where it is displayed (through museum_id). The subject table stores one row for every subject depicted in a work, linked back to the work table through work_id. The work table does not store the artist's name or nationality directly — it only stores an artist_id. The actual artist information is stored in the artist table.

Now suppose you wanted a list of all the artists who have created a work with the subject "Portrait." What would you have to do to retrieve this information? Here are the steps:

1. Retrieve the work_id of all works with a subject of "Portrait."
2. Retrieve the artist_id of all the artists who created the works listed in the work_ids returned in the previous step.
3. Retrieve the artist information for all the artist_ids returned in the previous step.

Each of these steps can be executed as a separate query. By doing so, you use the results returned by one SELECT statement to populate the WHERE clause of the next SELECT statement.

You can also use subqueries to combine all three queries into one single statement.

The first SELECT statement should be self-explanatory by now. It retrieves the work_id column for all subject entries with a subject of "Portrait." The output lists the works containing this subject:

SELECT work_id
FROM subject
WHERE subject = 'Portrait';

Now that we know which works have the subject "Portrait," the next step is to retrieve the artist IDs associated with those works, say work_id 210 and 224. You can create a SELECT statement as follows:

SELECT artist_id
FROM work
WHERE work_id IN (210, 224);

Now, combine the two queries by turning the first (the one that returned the work IDs) into a subquery. Look at the following SELECT statement:

SELECT artist_id
FROM work
WHERE work_id IN (SELECT work_id
                  FROM subject
                  WHERE subject = 'Portrait');

Subqueries are always processed starting with the innermost SELECT statement and working outward. When the preceding SELECT statement is processed, the DBMS actually performs two operations.

It first runs the following subquery:

SELECT work_id FROM subject WHERE subject = 'Portrait'

That query returns the work IDs for every work with the subject "Portrait" (in this case, 210 and 224). Those values are then passed to the WHERE clause of the outer query in the comma-delimited format required by the IN operator. The outer query now becomes:

SELECT artist_id FROM work WHERE work_id IN (210, 224)

As you can see, the output is correct and exactly the same as the output returned by the hard-coded WHERE clause above.

You now have the IDs of all the artists who created a work with the subject "Portrait." The next step is to retrieve the artist information for each of those artist IDs. You can use a chat with an AI assistant to write the full, combined SELECT statement:

Try the SQL statement.

SELECT full_name, nationality
FROM artist
WHERE artist_id IN (SELECT artist_id
                    FROM work
                    WHERE work_id IN (SELECT work_id
                                      FROM subject
                                      WHERE subject = 'Portrait'));

To execute this SELECT statement, the DBMS had to actually perform three SELECT statements. The innermost subquery returned a list of work IDs that were then used as the WHERE clause for the subquery above it. That subquery returned a list of artist IDs that were used as the WHERE clause for the top-level query. The top-level query actually returned the desired data.

As you can see, using subqueries in a WHERE clause enables you to write extremely powerful and flexible SQL statements. There is no limit imposed on the number of subqueries that can be nested, although in practice you will find that performance will tell you when you are nesting too deeply.

13.3 Using Subqueries as Calculated Fields

Another way to use subqueries is in creating calculated fields. Suppose you wanted to display the total number of works created by every artist in your artist table. Works of art are stored in the work table along with the appropriate artist_id.

To perform this operation, follow these steps:

1. Retrieve the list of artists from the artist table.
2. For each artist retrieved, count the number of associated works in the work table.

As you learned in previous lessons, you can use SELECT COUNT(*) to count rows in a table, and by providing a WHERE clause to filter a specific artist_id, you can count just that artist's works. For example, the following code counts the number of works created by the artist Pierre-Auguste Renoir (artist_id 500):

SELECT COUNT(*) AS number_of_works
FROM work
WHERE artist_id = 500;

To perform that COUNT() calculation for each artist, use COUNT() as a subquery. Look at the following code:

SELECT full_name,
       nationality,
       (SELECT COUNT(*)
        FROM work
        WHERE work.artist_id = artist.artist_id) AS number_of_works
FROM artist
ORDER BY full_name;

This SELECT statement returns three columns for every artist in the artist table: full_name, nationality, and number_of_works. number_of_works is a calculated field that is set by a subquery that is provided in parentheses. That subquery is executed once for every artist retrieved. If there are 421 artists in the artist table, the subquery is executed 421 times.

The WHERE clause in the subquery is a little different from the WHERE clauses used previously because it uses fully qualified column names; instead of just a column name (artist_id), it specifies the table and the column name (as work.artist_id and artist.artist_id). The following WHERE clause tells SQL to compare the artist_id in the work table to the one currently being retrieved from the artist table:

WHERE work.artist_id = artist.artist_id

This syntax — the table name and the column name separated by a period — must be used whenever there is possible ambiguity about column names. In this example, there are two artist_id columns, one in artist and one in work. Without fully qualifying the column names, the DBMS assumes you are comparing the artist_id in the work table to itself. Because:

SELECT COUNT(*) FROM work WHERE artist_id = artist_id

will always return the total number of works in the work table (the condition is true for every row), the results will not be what you expected:

SELECT full_name,
       nationality,
       (SELECT COUNT(*)
        FROM work
        WHERE artist_id = artist_id) AS number_of_works
FROM artist
ORDER BY full_name;

Although subqueries are extremely useful in constructing this type of SELECT statement, care must be taken to properly qualify ambiguous column names.

13.4 Summary

In this lesson, you learned what subqueries are and how to use them. The most common uses for subqueries are in WHERE clause IN operators and for populating calculated columns. You saw examples of both of these types of operations.