SQL for the Potterhead: Outer Joins

In this article we will cover the Left Outer Join as well as the Right Outer Join. Let's dive in!

Hagrid has some extra pets, and he's thinking about giving some away as Christmas gifts. To do this, he wants to find the names of all the students who don't have pets. Can we help him?

We want all the students who don't have pets. Let's start by visualizing this:

Looking at the diagram, this looks very similar to the one for our left join, except that we now want to exclude data that is present in both the tables.

Let's start writing the query.

1.First, we will write a left join. This will give us all the wizards, and attach their pets if they have them:

postgres=#
SELECT * FROM wizard LEFT JOIN pet ON wizard.id = pet.owner_id;
 id |        name        |   house    | id |    name     | species | owner_id 
----+--------------------+------------+----+-------------+---------+----------
  1 | Neville Longbottom | Gryffindor |  2 | Trevor      | toad    |        1
  2 | Ronald Weasley     | Gryffindor |  1 | Scabbers    | rat     |        2
  3 | Harry Potter       | Gryffindor |  3 | Hedwig      | owl     |        3
  5 | Seamus Finnigan    | Gryffindor |    |             |         |         
  6 | Hermione Granger   | Gryffindor |  4 | Crookshanks | cat     |        6
  4 | Draco Malfoy       | Slytherin  |  5 | unknown      | owl     |        4
(6 rows)

All 6 rows in our wizards table are present. If a wizard has a pet, they have been added.

2.Looking at the data above, you might notice that the owner_id is blank for the wizards that did not have pets. Let's use that to filter down to just the wizards that have no pets:

SELECT * FROM wizard LEFT JOIN pet ON wizard.id = pet.owner_id WHERE pet.owner_id IS NULL;
 id |      name       |   house    | id | name | species | owner_id 
----+-----------------+------------+----+------+---------+----------
  5 | Seamus Finnigan | Gryffindor |    |      |         |         
(1 row)

3.Let's break down this query to make sure we understand it

SELECT *: We want to select all the fields from the tables
FROM wizard LEFT JOIN pet: We want to JOIN the wizard and pet table. We are using a left join, where the wizards table is to the left hand side, and the pets table is to the right hand side
ON wizard.id = pet.owner_id: This is the join condition, which tells our query how to join the results
WHERE pet.owner_id IS NULL; We only want to select the rows where the pet's owner_id is NULL

According to our dataset, Seamus Finnigan is the only wizard without a pet, which should match our understanding of the Harry Potter Universe.

3.2 Right Outer Join:

Hagrid is very worried about abandoned pets, and wants to make sure that all pets without owners are being fed. Can we help him find all the pets that don't have owners listed?

Let's start by visualizing the data we need, using a set diagram:

This is very similar to the example we just discussed with the LEFT OUTER JOIN above. Let's try and write some SQl.

Let's write a RIGHT JOIN, that will give us all the pets, and attach their owners if they have them:

postgres=#                                                                                                     
SELECT * FROM wizard RIGHT JOIN pet ON wizard.id = pet.owner_id;
 id |        name        |   house    | id |    name     | species | owner_id 
----+--------------------+------------+----+-------------+---------+----------
  1 | Neville Longbottom | Gryffindor |  2 | Trevor      | toad    |        1
  2 | Ronald Weasley     | Gryffindor |  1 | Scabbers    | rat     |        2
  3 | Harry Potter       | Gryffindor |  3 | Hedwig      | owl     |        3
  6 | Hermione Granger   | Gryffindor |  4 | Crookshanks | cat     |        6
  4 | Draco Malfoy       | Slytherin  |  5 | unknown      | owl     |        4
    |                    |            |  7 | Brodwin     | owl     |       10
    |                    |            |  6 | Norbert     | Dragon  |      100
(7 rows)

The pet's without owners have the wizard.id field blank. Let's use this to just return the pet's with no listed owners:

postgres=# 
SELECT pet.name FROM wizard RIGHT JOIN pet ON wizard.id = pet.owner_id WHERE wizard.name IS null;
  name   
---------
 Brodwin
 Norbert
(2 rows)

That looks good! Hedwig, Scabbers, Crookshanks and Malfoy's nameless owl are absent from this list!

Let's examine the SQL:

SELECT pet.name : Select only the pet's name
FROM wizard : the table on the left
RIGHT JOIN pet : the table on the right
ON wizard.id = pet.owner_id : the join clause, which specifies how we want the rows from the two tables to be joined
WHERE wizard.name IS null; : We want to filter out all the rows that are present in both tables. This will leave behind only the rows that are exclusive to the pet table