Learning Mysql

-> WHERE album_name = "In A Silent Way";
+-------------+
| artist_name |
+-------------+
| Miles Davis |
+-------------+
1 row inset 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 inset 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 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 examples 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 inset sec)

Download 4.24 Mb.

Share with your friends: