Advanced Querying - Learning MySQL - page 252

You can then use the output as input to another query to find the track name:
mysql> SELECT track_name FROM track INNER JOIN played
-> USING (artist_id, album_id, track_id)
-> WHERE played = "2006-08-15 14:33:57";
+------------+
| track_name |
+------------+
| New Blues |
+------------+
1 row in set (0.31 sec)
In “User Variables,” later in this chapter, we'll show how you can use variables to avoid
having to type in the value in the second query.
With a nested query, you can do both steps in one shot:
mysql> SELECT track_name FROM track INNER JOIN played
-> USING (artist_id, album_id, track_id)
-> WHERE played = (SELECT MAX(played) FROM played);
+------------+
| track_name |
+------------+
| New Blues |
+------------+
1 row in set (0.28 sec)
You can see the nested query combines the two previous queries. Rather than using the
constant date and time value discovered from a previous query, it executes the query
directly as a subquery. This is the simplest type of nested query, one that returns a
scalar operand —that is, a single value.
The previous example used the equality operator, the equals sign, = . You can use all
types of comparison operators: < (less than), <= (less than or equal to), > (greater than),
>= (greater than or equal to), and != (not equals) or <> (not equals).
The ANY, SOME, ALL, IN, and NOT IN Clauses
Before we start to show some more advanced features of nested queries, we need to
create two new tables to use in our examples. Unfortunately, our music database is a
little too simple to effectively demonstrate the full power of nested querying. So, let's
extend the database to give us something to play with.
We'll create two new tables that share common data, but store different types of facts.
The first table we'll create contains information about producers—that is, the people
who oversee the music recording process. Here's the structure and some data:
mysql> CREATE TABLE producer (
-> producer_id SMALLINT(4) NOT NULL DEFAULT 0,
-> producer_name CHAR(128) DEFAULT NULL,
-> years SMALLINT(3) DEFAULT 0,
-> PRIMARY KEY (producer_id));