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.

References

Tags: sql postgres oracle