Advanced Querying - Learning MySQL - page 259

This time, the inner query is true but the NOT EXISTS clause negates it to give false. Since
it's false, the outer query doesn't produce results.
You'll notice that the subquery begins with SELECT * FROM artist . It doesn't actually
matter what you select in an inner query when you're using the EXISTS clause, since it's
not used by the outer query anyway. You can select one column, everything, or even a
constant (as in SELECT "cat" from artist ), and it'll have the same effect. Traditionally,
though, you'll see most SQL authors write SELECT * by convention.
Correlated subqueries
So far, it's difficult to imagine what you'd do with the EXISTS or NOT EXISTS clauses.
This section shows you how they're really used, illustrating the most advanced type of
nested query that you'll typically see in action.
Let's think about a realistic information need you might want to answer from the
music database. Suppose you want a list of all artists who've produced a self-titled
album. You can do this easily with a join query, which we recommend you try to think
about before you continue. You can also do it with the following nested query that uses
a correlated subquery :
mysql> SELECT artist_name FROM artist WHERE EXISTS
-> (SELECT * FROM album WHERE album_name = artist_name);
Empty set (0.28 sec)
There's no output because there are no self-titled albums. Let's add an artist with a self-
titled album and try again:
mysql> INSERT INTO artist VALUES (7, "The Beatles");
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO album VALUES (7, 1, "The Beatles");
Query OK, 1 row affected (0.14 sec)
Now the query:
mysql> SELECT artist_name FROM artist WHERE EXISTS
-> (SELECT * FROM album WHERE album_name = artist_name);
+-------------+
| artist_name |
+-------------+
| The Beatles |
+-------------+
1 row in set (0.17 sec)
So, the query works; now, we just need to understand how!
Let's examine the subquery in our previous example. You can see that it lists only the
album table in the FROM clause, but it uses a column from the artist table in the WHERE
clause. If you run it in isolation, you'll see this isn't allowed:
mysql> SELECT * FROM album WHERE album_name = artist_name;
ERROR 1054 (42S22): Unknown column 'artist_name' in 'where clause'