Learning MySQL

Nested Queries

Nested queries—supported by MySQL since version 4.1—are the most difficult to learn. However, they provide a powerful, useful, and concise way of expressing difficult information needs in short SQL statements. This section explains them, beginning with simple examples and leading to the more complex features of the EXISTS and IN statements. At the conclusion of this section, you’ll have completed everything this book contains about querying data, and you should be comfortable understanding almost any SQL query you encounter.

Nested Query Basics

You know how to find the name of an artist who made a particular album using an INNER JOIN:

mysql> SELECT artist_name FROM
    -> artist INNER JOIN album USING (artist_id)
    -> WHERE album_name = "In A Silent Way";
+-------------+
| artist_name |
+-------------+
| Miles Davis |
+-------------+
1 row in set (0.14 sec)

But there’s another way, using a nested query:

mysql> SELECT artist_name FROM artist WHERE artist_id =
    -> (SELECT artist_id FROM album WHERE album_name = "In A Silent Way");
+-------------+
| artist_name |
+-------------+
| Miles Davis |
+-------------+
1 row in set (0.28 sec)

It’s called a nested query because one query is inside another. The inner query, or subquery—the one that is nested—is written in parentheses, and you can see that it determines the artist_id for the album with the name In A Silent Way. The parentheses are required for inner queries. The outer query is the one that’s listed first and isn’t parenthesized ...