SQL Joins
October 1, 2012
I've always struggled understanding the difference between the different types of join available in SQL. It does, however, make much more sense now I've looked at the new style join syntax.
Table Structure
Given the following tables:
abm_serial | abm_name
------------+-----------------------
1 | Dark Side of the Moon
2 | A Night at the Opera
3 | A Day at the Races
6 | Brave
trk_serial | trk_name | trk_album
------------+----------------------+-----------
1 | On The Run | 1
2 | Money | 1
3 | Us and Them | 1
4 | Death On Two Legs | 2
5 | Bohemian Rhapsody | 2
6 | Seaside Rendezvous | 2
7 | Tie Your Mother Down | 3
8 | White Man | 3
9 | Somebody To Love | 3
10 | Made Again |
You'll note that there are four albums, three of which have tracks. There is also a track without an album.
Inner Join
The word inner in this query is optional, it's the default type for a join.
select abm_name, trk_name from track inner join album on trk_album = abm_serial
Assuming a Venn diagram for the two tables, this returns the intersection - the data that has entries on both sides. It therefore doesn't return the album without tracks, or the track without an album.
abm_name | trk_name -----------------------+---------------------- Dark Side of the Moon | Us and Them Dark Side of the Moon | Money Dark Side of the Moon | On The Run A Night at the Opera | Seaside Rendezvous A Night at the Opera | Bohemian Rhapsody A Night at the Opera | Death On Two Legs A Day at the Races | Somebody To Love A Day at the Races | White Man A Day at the Races | Tie Your Mother Down (9 rows)
Full Outer Join
This is the opposite of the inner join - it returns data from both tables, whether they link or not, but does link where possible.
select abm_name, trk_name from track full outer join album on trk_album = abm_serial
This produces the following result:
abm_name | trk_name -----------------------+---------------------- Dark Side of the Moon | On The Run Dark Side of the Moon | Money Dark Side of the Moon | Us and Them A Night at the Opera | Death On Two Legs A Night at the Opera | Bohemian Rhapsody A Night at the Opera | Seaside Rendezvous A Day at the Races | Tie Your Mother Down A Day at the Races | White Man A Day at the Races | Somebody To Love <NULL> | Made Again Brave | <NULL> (11 rows)
Left Outer Join
This only returns rows from the left hand side of the join.
select abm_name, trk_name from track left outer join album on abm_serial = trk_album
The left hand side is the original table selected from, so all tracks are returned, whether they have an album or not.
abm_name | trk_name -----------------------+---------------------- Dark Side of the Moon | On The Run Dark Side of the Moon | Money Dark Side of the Moon | Us and Them A Night at the Opera | Death On Two Legs A Night at the Opera | Bohemian Rhapsody A Night at the Opera | Seaside Rendezvous A Day at the Races | Tie Your Mother Down A Day at the Races | White Man A Day at the Races | Somebody To Love <NULL> | Made Again (10 rows)
Right Outer Join
The opposite to this is to take the rows from the right hand side of the join; the table joined to.
select abm_name, trk_name from track right outer join album on abm_serial = trk_album
This returns albums without tracks, but not tracks without albums.
abm_name | trk_name -----------------------+---------------------- Dark Side of the Moon | Us and Them Dark Side of the Moon | Money Dark Side of the Moon | On The Run A Night at the Opera | Seaside Rendezvous A Night at the Opera | Bohemian Rhapsody A Night at the Opera | Death On Two Legs A Day at the Races | Somebody To Love A Day at the Races | White Man A Day at the Races | Tie Your Mother Down Brave | <NULL> (10 rows)
Contrarywise
The left and right outer joins can be turned the other way around, and this will reverse the direction. For example, here's the right outer join example:
select abm_name, trk_name from track right outer join album on abm_serial = trk_album
If we switch the tables around, and select from album initially, the direction of the join changes.
select abm_name, trk_name from album left outer join track on abm_serial = trk_album
Old Style Join Syntax
The styles of join shown above are from a newer SQL standard. There is also the old way of doing things. For an inner join, this involves specifying a list of tables to select from and adding the join in the where clause.
So for this inner join:
select abm_name, trk_name from track inner join album on trk_album = abm_serial
the equivalent is
select abm_name, trk_name from track, album where trk_album = abm_serial
With outer joins, Oracle has a proprietry syntax, adding (+) to the side of the join that is allowed to be null.
For example:
select abm_name, trk_name from track, album where trk_album = abm_serial (+)
This does an outer join and will include rows from the track table where there is no link to a row in album.
To do it the other way around:
select abm_name, trk_name from track, album where trk_album (+) = abm_serial
This does an outer join and will include rows from the album table where there is no link to a row in track.
