Doing More with MySQL - Learning MySQL - page 289

With the techniques we've described so far in the topic, there's no way of doing this
without creating a table that combines the two tables (perhaps using INSERT with
SELECT ), removing unwanted rows, and copying the data back to its source. In fact, this
is exactly what you had to do prior to MySQL 4.0. This section shows you how you
can perform this procedure and other more advanced types of deletion in recent ver-
sions of MySQL.
Consider the query you need to write to find tracks you've never played. One way to
do it is to use a nested query—following the techniques we showed you in Chapter 7
—with the NOT EXISTS clause. Here's the query:
mysql> SELECT track_name FROM track WHERE NOT EXISTS
-> (SELECT * FROM played WHERE
-> track.artist_id = played.artist_id AND
-> track.album_id = played.album_id AND
-> track.track_id = played.track_id);
+----------------------------+
| track_name |
+----------------------------+
| Do You Love Me? |
| Nobody's Baby Now |
| Loverman |
| Jangling Jack |
| Red Right Hand |
| I Let Love In |
| Thirsty Dog |
| Ain't Gonna Rain Anymore |
| Lay Me Low |
| Do You Love Me? (Part Two) |
...
+----------------------------+
We've shown only 10 tracks from the output, but there are actually 142 tracks we've
never listened to. You can probably see how the query works, but let's briefly discuss
it anyway before we move on. You can see it uses a correlated subquery, where the
current row being processed in the outer query is referenced by the subquery; you can
tell this because three columns from track are referenced, but the track table isn't listed
in the FROM clause of the subquery. The subquery produces output when there's a row
in the played table that matches the current row in the outer query (and so there's a
track that's been played). However, since the query uses NOT EXISTS , the outer query
doesn't produce output when this is the case, and so the overall result is that rows are
output for tracks that haven't been played.
Now let's take our query and turn it into a DELETE statement. Here it is:
mysql> DELETE track FROM track WHERE NOT EXISTS
-> (SELECT * FROM played WHERE track.artist_id = played.artist_id AND
-> track.album_id = played.album_id AND
-> track.track_id = played.track_id);
Query OK, 142 rows affected (0.01 sec)