3 Table SQL Joins
October 1, 2012
Following on from http://drumcoder.co.uk/blog/2012/oct/01/sql-joins/, I wanted to do the same process with three tables.
Our artist and album tables now look like this:
abm_serial | abm_name | abm_artist
------------+-----------------------+------------
1 | Dark Side of the Moon | 1
6 | Brave | 2
2 | A Night at the Opera | 3
3 | A Day at the Races | 3
6 | Voyage 34 | <NULL>
(4 rows)
art_serial | art_name
------------+------------
1 | Pink Floyd
2 | Marillion
3 | Queen
4 | Shinedown
(4 rows)
Inner Joins
The following query joins the three tables together using inner joint, producing a list where both links are valid:
select art_name, abm_name, trk_name from album inner join artist on art_serial = abm_artist inner join track on abm_serial = trk_album
Produces the following output:
art_name | abm_name | trk_name ------------+-----------------------+---------------------- Pink Floyd | Dark Side of the Moon | Us and Them Pink Floyd | Dark Side of the Moon | Money Pink Floyd | Dark Side of the Moon | On The Run Queen | A Night at the Opera | Seaside Rendezvous Queen | A Night at the Opera | Bohemian Rhapsody Queen | A Night at the Opera | Death On Two Legs Queen | A Day at the Races | Somebody To Love Queen | A Day at the Races | White Man Queen | A Day at the Races | Tie Your Mother Down (9 rows)
Including Tracks not linked to Albums
To include the tracks that are not linked to a specific album, we can right outer join that link:
select art_name, abm_name, trk_name from album inner join artist on art_serial = abm_artist right outer join track on abm_serial = trk_album
produces:
art_name | abm_name | trk_name ------------+-----------------------+---------------------- Pink Floyd | Dark Side of the Moon | On The Run Pink Floyd | Dark Side of the Moon | Money Pink Floyd | Dark Side of the Moon | Us and Them Queen | A Night at the Opera | Death On Two Legs Queen | A Night at the Opera | Bohemian Rhapsody Queen | A Night at the Opera | Seaside Rendezvous Queen | A Day at the Races | Tie Your Mother Down Queen | A Day at the Races | White Man Queen | A Day at the Races | Somebody To Love <NULL> | <NULL> | Made Again (10 rows)
Albums without Tracks
If we flip this around to be a left outer join, we include albums without tracks:
select art_name, abm_name, trk_name from album inner join artist on art_serial = abm_artist left outer join track on abm_serial = trk_album
produces:
art_name | abm_name | trk_name ------------+-----------------------+---------------------- Pink Floyd | Dark Side of the Moon | Us and Them Pink Floyd | Dark Side of the Moon | Money Pink Floyd | Dark Side of the Moon | On The Run Marillion | Brave | <NULL> Queen | A Night at the Opera | Seaside Rendezvous Queen | A Night at the Opera | Bohemian Rhapsody Queen | A Night at the Opera | Death On Two Legs Queen | A Day at the Races | Somebody To Love Queen | A Day at the Races | White Man Queen | A Day at the Races | Tie Your Mother Down (10 rows)
Full Details
Two full outer joins produce a full list of details with missing links represented by null.
select art_name, abm_name, trk_name from track full outer join album on abm_serial = trk_album full outer join artist on art_serial = abm_artist
produces:
art_name | abm_name | trk_name ------------+-----------------------+---------------------- Pink Floyd | Dark Side of the Moon | On The Run Pink Floyd | Dark Side of the Moon | Us and Them Pink Floyd | Dark Side of the Moon | Money Marillion | Brave | <NULL> Queen | A Day at the Races | White Man Queen | A Day at the Races | Somebody To Love Queen | A Day at the Races | Tie Your Mother Down Queen | A Night at the Opera | Death On Two Legs Queen | A Night at the Opera | Bohemian Rhapsody Queen | A Night at the Opera | Seaside Rendezvous <NULL> | <NULL> | Made Again <NULL> | Voyage 34 | <NULL> Shinedown | <NULL> | <NULL>
