Before you keep reading...
Runestone Academy can only continue if we get support from individuals like you. As a student you are well aware of the high cost of textbooks. Our mission is to provide great books to you for free, but we ask that you consider a $10 donation, more if you can or less if $10 is a burden.
Before you keep reading...
Making great stuff takes time and $$. If you appreciate the book you are reading now and want to keep quality materials free for other students please consider a donation to Runestone Academy. We ask that you consider a $10 donation, but if you can give more thats great, if $10 is too much for your budget we would be happy with whatever you can afford as a show of support.
A subquery is simply a SELECT query enclosed with parentheses and nested within another query or statement. This subquery expression evaluates to a scalar, a row, a column, or a table, depending on the query results and the context in which the subquery appears. In this chapter we discuss the many ways in which we can use the result of a query within another query.
1.8.1. Tables used in this chapter¶
For this chapter we will be using the books dataset (tables books, authors, etc.), described in Appendix A.
1.8.2. Scalars, rows, and tables¶
Before we discuss the uses of subqueries, it is useful to talk about some additional types of data that come up in SQL. So far we have assumed that all expressions evaluate to a scalar value. Scalar values are simple values of a single type, such as
'hello'. We can also work with row values in SQL. A row is just an ordered list of values. We can write down a literal row (SQL calls these “row value constructors”) by putting a comma-separated list of expressions between parentheses:
(1, 'hello', 3.1415)
In most databases (but not SQLite), you can SELECT the above expression as a literal, with the result showing as a single column.
Rows can be used in comparison expressions. The two queries below are equivalent (although the second is probably preferred as a matter of style):
We will see more useful applications of row expressions in the following sections.
Beyond rows, we can also think in terms of tables as values. Here we are using “tables” to mean a collection of rows, not necessarily the named object living in the database. The result from a SELECT query is a table in this sense.
1.8.3. Boolean expressions using subqueries¶
To start with, we will examine Boolean expressions using subqueries. These are appropriate for use within the WHERE clause of another query or statement.
188.8.131.52. Scalar or row result¶
We can use a subquery in place of a scalar expression as long as we know the subquery will return a single row and column. Let us return to an example from Chapter 1.4 using our books table: how can we find all books published in the same year as The Three-Body Problem? We will use a subquery (the SELECT query inside the parentheses below) to first obtain the publication_year of The Three-Body Problem, and then we will use that result to get our list of books:
To see what is happening in this query, first execute the subquery on its own:
SELECT publication_year FROM books WHERE title = 'The Three-Body Problem';
Since this particular query returns exactly one row and one column, we treat the result (the integer
2008) as a scalar value, and simply substitute the scalar in place of the subquery:
SELECT * FROM books WHERE publication_year = 2008;
Note that this only works because we only have one book with the title The Three-Body Problem. In general, it is not a good idea to assume a query will return a single row unless you use a WHERE clause condition on a column known to hold unique values, or unless you are computing an aggregate statistic over a set of rows (we will discuss aggregates in Chapter 1.9). If multiple rows are returned by the subquery, the query will result in an error. However, if zero rows are returned from the subquery, the result is considered to be
NULL, rather than an error.
This same approach works with row expressions, although the syntax is perhaps a bit inconsistent. If a subquery would return multiple columns, then you need to use a row expression on the left-hand side of your comparison only. That is, the below is correct SQL:
SELECT * FROM books WHERE (author_id, publication_year) = (SELECT author_id, publication_year FROM books WHERE title = 'The Hundred Thousand Kingdoms') ;
Putting parentheses around the columns in the SELECT clause of the subquery will cause an error.
Comparisons with scalar results do not have to be equality; you can use any comparison operator instead:
SELECT * FROM books WHERE publication_year > (SELECT publication_year FROM books WHERE title = 'Americanah') ;
184.108.40.206. Table or column result¶
When a query can return multiple rows (a column or table), we have a different set of operators to work with. In this section, we discuss the IN operator and the use of comparison operators with ALL, ANY, and SOME. Another Boolean operator, EXISTS, will wait until we discuss correlated subqueries later in the chapter. All of the operators that work with multiple rows also work on subqueries which return zero rows or one row.
The IN operator lets us compare some expression to every row returned from a subquery. If the expression equals any result from the subquery, then the IN expression evaluates to
True. For example, we can ask our database for a list of books which have won awards (books with book ids matching some book id in the books_awards table):
SQL also provides the NOT IN operator as simply the Boolean inverse of IN. We can get a list of books that did not win any of the awards listed in our database by a simple modification of the above query:
SELECT * FROM books WHERE book_id NOT IN (SELECT book_id FROM books_awards) ;
The IN operator also works with row expressions, when we want to compare against multiple column subquery results. Here is a query that asks for books published in the same year as the author’s death. (We are using the substring function as implemented by SQLite to get just the first four characters of each author’s death date. Although the substring is a character string and book publication years are stored as integers, SQLite is able to do an appropriate type conversion to make the comparison.)
SELECT a.name AS author, b.title, b.publication_year FROM authors AS a JOIN books AS b ON a.author_id = b.author_id WHERE (a.author_id, b.publication_year) IN (SELECT author_id, substring(death, 1, 4) FROM authors) ;
As always, it can be helpful to execute the subquery separately to see what values it returns in order to better understand what the entire query is doing.
IN also has a useful application that does not involve a subquery. If we follow IN with a comma-separated list of expressions inside parentheses, the operator will test the expression to the left of IN against every expression listed in the parentheses. Note that while the expression list looks like a row expression, it is very different; every expression in the list after IN should have a type compatible with the expression being compared.
For example, we might be interested in books by a few different authors:
SELECT a.name AS author, b.title FROM books AS b JOIN authors AS a ON a.author_id = b.author_id WHERE author IN ('Virginia Woolf', 'Kazuo Ishiguro', 'Iris Murdoch');
If we want to compare multiple values (i.e., row expressions), we must use parentheses for each expression. In this case, the general form of the expression is
(expr1, expr2, ...) IN ((test11, test12, ...), (test21, test22, ...), ...)
220.127.116.11.2. ALL, ANY, and SOME¶
We can alternately use comparison operators in conjunction with the ALL or ANY or SOME keywords to compare an expression against the results of a subquery. For example, we can ask again for books which have won awards by using the equality operator together with the ANY keyword as follows (note that the ALL/ANY/SOME keywords are not supported by SQLite, so you cannot test this within the textbook’s interactive tools):
SELECT * FROM books WHERE book_id = ANY (SELECT book_id FROM books_awards);
SOME is just a synonym for ANY. The IN operator when used with subqueries is equivalent to = ANY. However, ANY cannot be used with an expression list in the same way IN can.
In contrast, ALL requires that every row returned the subquery passes the comparison test. For example, to find books published before all books by the author Willa Cather:
SELECT * FROM books WHERE publication_year < ALL (SELECT publication_year FROM books WHERE author_id = (SELECT author_id FROM authors WHERE name = 'Willa Cather') ) ;
Note here we have used a subquery inside another subquery! We can nest subqueries in this fashion; we can also use multiple subqueries within a compound Boolean expression.
The NOT IN operator is equivalent to <> ALL.
18.104.22.168. Use in statements¶
Subqueries do not have to be used only within other SELECT queries. The use of subqueries within the WHERE clause of DELETE or UPDATE statements can be very powerful, often making up for the fact that we cannot do joins within those types of statements. For example, we could use a subquery to remove any authors from our database for whom we have no books:
DELETE FROM authors WHERE author_id NOT IN (SELECT author_id FROM books);
There are no rows matching this condition in the database (unless you add them), so the above query does not remove any rows, although it runs successfully.
1.8.5. Subqueries in other clauses¶
We have seen numerous examples of subqueries used in WHERE clauses. However, subquery expressions can be used in other contexts. In particular, subqueries returning scalars can be useful in SELECT clauses and in the SET clauses of UPDATE statements. Subqueries returning tables can also be used in place of named tables in the FROM clause of a SELECT clause.
Used in a SELECT clause, subqueries can be used to retrieve values that are not easily obtained from the tables used in the outer query. Used in this way, the subquery must return a scalar. These subqueries are almost always correlated, as we want to return a value that is specific to each row.
For example, in a listing of books, we might want to include the total number of books written by the author. For this we will use the aggregate expression COUNT(*), which simply counts the number of rows matching the WHERE clause in a SELECT query 1. (Aggregates are discussed fully in Chapter 1.9.)
Used in the SET clause of an UPDATE statement, subqueries provide a way to work around the issue that we cannot use joins in an UPDATE. If we want to update rows in some table with data from a second table, we can simply use a subquery to obtain the proper value.
As an example, in preparation of this book’s database, a statement was run to populate the publication_year column of books using book edition information. (The editions table in the database only has entries for a few books, to keep the size manageable, but the original database had complete data.) This statement uses another aggregate expression to obtain the earliest publication year from the editions table for each book:
UPDATE books SET publication_year = (SELECT MIN(publication_year) FROM editions WHERE books.book_id = editions.book_id) ;
Note: if you run the statement above, you will update most books to have a
NULL publication year - when the subquery returns zero rows, the result is interpreted as
NULL. (Do not worry if you executed this statement - changes are only made to a copy of the data. You can obtain an unmodified copy of the database by refreshing your browser window.) You can modify the statement to only update rows for which we have editions data using another subquery:
UPDATE books SET publication_year = (SELECT MIN(publication_year) FROM editions WHERE books.book_id = editions.book_id) WHERE EXISTS (SELECT 1 FROM editions WHERE books.book_id = editions.book_id) ;
Subqueries can also be used within the FROM clause of a SELECT query, in which case the subquery result acts like a table containing exactly the data returned by the subquery. In this usage, the subquery expression must be given a name using aliasing. The subquery cannot be correlated! The subquery expression can be used to obtain computed data not available in any table in the database. For example, above we used a correlated subquery to retrieve author’s book counts on a row-by-row basis to go with each book title. We could instead compute all author totals using an uncorrelated subquery, and then join each to the result as if it were a table. (Here the subquery uses both grouping and aggregation, covered in Chapter 1.9.)
SELECT a.name AS author, c.count AS author_total, b.title FROM authors AS a JOIN books AS b ON b.author_id = a.author_id JOIN (SELECT author_id, COUNT(*) AS count FROM books GROUP BY author_id) AS c ON c.author_id = a.author_id ;
1.8.6. Comparison with joins¶
Subqueries are comparable to joins in the sense that they both involve multiple tables. There are many cases in which a subquery can substitute for a join or vice-versa. However, there are some subtle differences.
First, of course, is that short of using SELECT clause subqueries, you can only return data that actually appears in the outer query’s tables. If you need your result to contain data contained in multiple tables, it is generally best to join the tables rather than using SELECT clause subqueries. (The example used above of a SELECT clause subquery is an exception, since the data we pulled in was not actually stored in any table.) Using a separate subquery for each column needed is unwieldy, hard to read, and probably inefficient.
On the other hand, if you are retrieving data from one table only, it is sometimes advantageous to use a subquery. Consider these two queries to retrieve books that have won awards:
Both queries return the same data, but the second query has duplicate rows - each book appears once for each award it has won. In the first query, the IN operator merely tests for the presence of a book in the awards table, not how many times it appears, so duplicates are avoided.
The NOT EXISTS and NOT IN operators are particularly interesting in that they can provide clean solutions to questions that otherwise require an outer join, such as listing the books which have not won awards.
In many cases, though, you have choices in how you approach a query. Which you use depends on your personal preference and style. Here are three different queries for finding books published in the same year as the author’s death - one using an uncorrelated subquery (repeated from above), one using a correlated subquery with EXISTS, and one using a join:
SELECT a.name AS author, b.title, b.publication_year FROM authors AS a JOIN books AS b ON a.author_id = b.author_id WHERE (a.author_id, b.publication_year) IN (SELECT author_id, substring(death, 1, 4) FROM authors) ; SELECT a1.name AS author, b.title, b.publication_year FROM authors AS a1 JOIN books AS b ON a1.author_id = b.author_id WHERE EXISTS (SELECT 1 FROM authors AS a2 WHERE a2.author_id = a1.author_id AND substring(a2.death, 1, 4) = b.publication_year) ; SELECT a1.name AS author, b.title, b.publication_year FROM authors AS a1 JOIN books AS b ON a1.author_id = b.author_id JOIN authors AS a2 ON a2.author_id = a1.author_id AND substring(a2.death, 1, 4) = b.publication_year ;
1.8.7. Self-check exercises¶
This section contains some exercises using the books data set (reminder: you can get full descriptions of all tables in Appendix A). If you get stuck, click on the “Show answer” button below the exercise to see a correct answer. There are many ways to answer these questions; try to use at least one subquery for each.
Write a query to list books (title, publication_year) by the author Viet Thanh Nguyen.
Write a query giving the author of How We Became Human.
Write a query to list authors born after the death of author Albert Camus.
Write a query to list books for which we have editions information.
Write a query to list the titles of books by living authors (assume a
NULL death date means the author is living).
Write a query to list the authors who have won the Nobel Prize in Literature (an author award).
Write a query to list the authors whose books have won any kind of Pulitzer prize (a book award starting with the string ‘Pulitzer’).
Write a query to list authors who have won book awards but not author awards.
Write a query to find books by authors with only one book (according to our database). Hint: one way is to ask, for each book, whether there exist other books by the same author.
Write a query to list all awards (either author awards or book awards) won by author J. M. Coetzee.
For this particular problem, we could instead use something called a window function, which will be discussed briefly in Chapter 1.11.