-> (SELECT * FROM artist WHERE artist_name = "John Coltrane")

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

Download 4.24 Mb.

Share with your friends: