Advanced Querying - Learning MySQL - page 258

subqueries are often used with the IN statement we've already discussed, and almost
always used with the EXISTS and NOT EXISTS clauses that are the focus of this section.
EXISTS and NOT EXISTS basics
Before we start on our discussion of correlated subqueries, let's investigate what the
EXISTS clause does. We'll need a simple but strange example to introduce the clause,
since we're not discussing correlated subqueries just yet. So, here goes: suppose you
want to find a list of all artists in the database, but only if the database is active (which
you've defined to mean only if at least one track from any album by any artist has been
played). Here's the query that does it:
mysql> SELECT * FROM artist WHERE EXISTS
-> (SELECT * FROM played);
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 3 | Miles Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
+-----------+---------------------------+
6 rows in set (0.18 sec)
The subquery returns all rows from the played table. However, what's important is that
it returns at least one row; it doesn't matter what's in the row, how many rows there
are, or whether the row contains only NULL values. So, you can think of the subquery
as being true or false, and in this case it's true because it produces some output. When
the subquery is true, the outer query that uses the EXISTS clause returns a row. The
overall result is that all rows in the artist table are displayed because, for each one,
the subquery is true.
Let's try a query where the subquery isn't true. Again, let's contrive a query: this time,
we'll output the names of all albums in the database, but only if we own at least one
album by John Coltrane. Here's the query:
mysql> SELECT album_name FROM album WHERE EXISTS
-> (SELECT * FROM artist WHERE artist_name = "John Coltrane");
Empty set (0.10 sec)
Since the subquery isn't true—no rows are returned because John Coltrane isn't in our
database—no results are returned by the outer query.
The NOT EXISTS clause does the opposite. Imagine you want a list of all producers if you
don't have an artist called New Order in the database. Here it is:
mysql> SELECT * FROM producer WHERE NOT EXISTS
-> (SELECT * FROM artist WHERE artist_name = "New Order");
Empty set (0.16 sec)