Databases Reference
In-Depth Information
mysql>
SELECT artist_name, album_name, COUNT(*) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> INNER JOIN played USING (artist_id, album_id, track_id)
-> GROUP BY album.artist_id, album.album_id;
+-------------+----------------------------+----------+
| artist_name | album_name | COUNT(*) |
+-------------+----------------------------+----------+
| New Order | Retro - Miranda Sawyer POP | 8 |
| Miles Davis | Live Around The World | 3 |
+-------------+----------------------------+----------+
2 rows in set (0.11 sec)
Here's how you modify that query to use a left join to list all albums, even those that
have never been played:
mysql>
SELECT artist_name, album_name, COUNT(played) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> LEFT JOIN played USING (artist_id, album_id, track_id)
-> GROUP BY album.artist_id, album.album_id;
+---------------------------+------------------------------------------+----------+
| artist_name | album_name | COUNT(*) |
+---------------------------+------------------------------------------+----------+
| New Order | Retro - John McCready FAN | 0 |
| New Order | Substance (Disc 2) | 0 |
| New Order | Retro - Miranda Sawyer POP | 8 |
| New Order | Retro - New Order / Bobby Gillespie LIVE | 0 |
| New Order | Power, Corruption & Lies | 0 |
| New Order | Substance 1987 (Disc 1) | 0 |
| New Order | Brotherhood | 0 |
| Nick Cave & The Bad Seeds | Let Love In | 0 |
| Miles Davis | Live Around The World | 3 |
| Miles Davis | In A Silent Way | 0 |
| The Rolling Stones | Exile On Main Street | 0 |
| The Stone Roses | Second Coming | 0 |
| Kylie Minogue | Light Years | 0 |
+---------------------------+------------------------------------------+----------+
13 rows in set (0.18 sec)
The only difference is that the final
INNER JOIN
is replaced by a
LEFT JOIN
, which means
that the data from the first two inner joins—of
artist
and
album
—drives the process.
The result is that all albums and their artists are displayed, along with the count of the
number of matching rows in the
played
table. You can see we haven't listened to the
majority of the albums.
We've shown you that it matters what comes before and after the
LEFT JOIN
statement.
Whatever is on the left drives the process, hence the name “left join.” If you really don't
want to reorganize your query so it matches that template, you can use
rollRIGHT
JOIN
. It's exactly the same, except whatever is on the right drives the process. Here's
our earlier
played
and
track
example written as a right join:
mysql>
SELECT track_name, played FROM
-> played RIGHT JOIN track USING (artist_id, album_id, track_id)
