Databases Reference
In-Depth Information
-> 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 paren-
theses are required for inner queries. The
outer query
is the one that's listed first and
isn't parenthesized here: you can see that it finds the
artist_name
of the the artist with
an
artist_id
that matches the result of the subquery. So, overall, the inner query finds
the
artist_id
, and the outer query uses it to find the artist's name.
So, which approach is preferable: nested or not nested? The answer isn't easy. In terms
of performance, the answer is usually not: nested queries are hard to optimize, and so
they're almost always slower to run than the unnested alternative. Does this mean you
should avoid nesting? The answer is no: sometimes it's your only choice if you want to
write a single query, and sometimes nested queries can answer information needs that
can't be easily solved otherwise. What's more, nested queries are expressive. Once
you're comfortable with the idea, they're a very readable way to show how a query is
evaluated. In fact, many SQL designers advocate teaching nested queries before the
join-based alternatives we've shown you in the past few chapters. We'll show you ex-
amples of where nesting is readable and powerful throughout this section.
Before we begin to cover the keywords that can be used in nested queries, let's visit an
example that can't be done easily in a single query—at least, not without MySQL's
proprietary
LIMIT
clause! Suppose you want to know which track you listened to most
recently. To do this, following the methods we've learned previously, you could find
the date and time of the most recently stored row in the
played
table:
mysql>
SELECT MAX(played) FROM played;
+---------------------+
| MAX(played) |
+---------------------+
| 2006-08-15 14:33:57 |
+---------------------+
1 row in set (0.00 sec)
