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 | +----------------------+-------------------+
Available Wiki Topics
The operator of this site makes no claims, promises, or guarantees of the accuracy, completeness, originality, uniqueness, or even general adequacy of the contents herein and expressly disclaims liability for errors and omissions in the contents of this website.