Learning Mysql

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 inset 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 inset 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 aright join:
mysql> SELECT track_name, played FROM

Download 4.24 Mb.

Share with your friends: