The EXISTS and NOT EXISTS Clauses

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 inset 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

Download 4.24 Mb.

Share with your friends: