About SQL Joins

Overview

An extremely brief explanation of the difference between join, left join, and right join.

Example Data

We're using these little data sets for our example.

mysql> select * from band;

+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Captain Beefheart |
|  2 | Dreng             |
|  3 | Singapore Sling   |
|  4 | Helium Balloon    |
+----+-------------------+

mysql> select * from album;

+----+---------+----------------------+
| id | band_id | name                 |
+----+---------+----------------------+
|  1 |       1 | Safe as Milk         |
|  2 |       2 | Drenge               |
|  3 |       3 | The Curse of Singapo |
|  4 |    NULL | Unreleased Comp      |
|  5 |       3 | Never Forever        |
+----+---------+----------------------+

Inner Join

I want to join each album to the corresponding band, while ignoring any bands without an album, and any albums that don't have an artist assigned to them.

SELECT album.name as Album, band.name as Band
FROM album
INNER JOIN band ON album.band_id = band.id;

+----------------------+-------------------+
| Album                | Band              |
+----------------------+-------------------+
| Safe as Milk         | Captain Beefheart |
| Drenge               | Dreng             |
| The Curse of Singapo | Singapore Sling   |
| Never Forever        | Singapore Sling   |
+----------------------+-------------------+

Note the band Helium Balloon and the Unreleased Comp album are ommitted.

Left Join

Here I want to get a list of all of the albums and join them to the band. If an album doesn't have a band, e.g. our Unreleased Comp, I still want to list it.

SELECT album.name as Album, band.name as Band
FROM album
LEFT JOIN band ON album.band_id = band.id;

+----------------------+-------------------+
| Album                | Band              |
+----------------------+-------------------+
| Safe as Milk         | Captain Beefheart |
| Drenge               | Dreng             |
| The Curse of Singapo | Singapore Sling   |
| Unreleased Comp      | NULL              |
| Never Forever        | Singapore Sling   |
+----------------------+-------------------+

Right Join

Here I want to get a list of all the bands and their albums. If a band doesn't have any albums, e.g., Helium Balloon, I still want to list that band.

SELECT album.name as Album, band.name as Band
FROM album
RIGHT JOIN band ON album.band_id = band.id;

+----------------------+-------------------+
| Album                | Band              |
+----------------------+-------------------+
| Safe as Milk         | Captain Beefheart |
| Drenge               | Dreng             |
| The Curse of Singapo | Singapore Sling   |
| Never Forever        | Singapore Sling   |
| NULL                 | Helium Balloon    |
+----------------------+-------------------+