-> track LEFT JOIN played USING (artist_id, album_id, track_id)

| Crystal | 2006-08-14 10:47:21 |
| Regret | 2006-08-14 10:43:37 |
| Ceremony | 2006-08-14 10:41:43 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| True Faith | 2006-08-14 10:30:25 |
| Temptation | 2006-08-14 10:25:22 |
| Fine Time | 2006-08-14 10:21:03 |
| Do You Love Me | NULL |
| Nobody's Baby Now | NULL |
| Loverman | NULL |
| Jangling Jack | NULL |
| Red Right Hand | NULL |
| I Let Love In | NULL You can see what happens tracks that have been played have dates and times, and those that haven’t don’t (the played value is NULL. We’ve added an ORDER BY played
DESC
to display the output from most to least recently played, where never played”
(
NULL
) is the smallest possible value.
The order of the tables in the LEFT JOIN
is important. If you reverse the order in the previous query, you get very different output:
mysql> SELECT track_name, played FROM
-> played LEFT JOIN track USING (artist_id, album_id, track_id)
-> ORDER BY played DESC;
+-----------------------+---------------------+
| track_name | played |
+-----------------------+---------------------+
| New Blues | 2006-08-15 14:33:57 |
| Intruder | 2006-08-15 14:26:12 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| Crystal | 2006-08-14 10:47:21 |
| Regret | 2006-08-14 10:43:37 |
| Ceremony | 2006-08-14 10:41:43 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| True Faith | 2006-08-14 10:30:25 |
| Temptation | 2006-08-14 10:25:22 |
| Fine Time | 2006-08-14 10:21:03 |
+-----------------------+---------------------+
11 rows inset sec)
In this, the query is driven by the played table, and so all rows from it are output matched against track values if they exist and
NULL
otherwise. Since all rows in the played table have matching tracks, no
NULL
values are shown. Importantly, because the played table drives the process, you don’t see all the rows from the track table (because not all tracks have been played).
In the introduction to this section, we motivated left joins with the example of listing all albums and the number of times they’ve been played, regardless of whether that value is zero. You’ll recall from The GROUP BY Clause the following query that shows you that information, but only for albums you’ve played:

Download 4.24 Mb.

Share with your friends: