SQL for the Potterhead: Inner Joins

Luna Lovegood wants to send a letter but none of the school owls are available. Can we write a query that will return the name of all the students who have owls, so she can find one to borrow??

Let's start by thinking of the data we need from each table:

1. From the pet table, we want all the pets that are owls
2. From the wizard table, we want all the wizards who have pets that are owls

Drawing a quick venn diagram, the relationship might look something like this:

Since we are only interested in data present in both tables, we will write an inner join. Let's write some SQL:

1. First, let's get all pets that are owls:

postgres=# 
SELECT * FROM pet WHERE species = 'owl';
 id |  name   | species | owner_id 
----+---------+---------+----------
  3 | Hedwig  | owl     |        3
  5 | unknown  | owl     |        4
  7 | Brodwin | owl     |       10
(3 rows)

Our query returned 3 owls. We can't just send someone's owl on an errand, so we will have to join this with the owners table

1. Let's write a JOIN:

postgres=# 
SELECT * FROM pet JOIN wizard ON wizard.id = pet.owner_id WHERE pet.species = 'owl';
 id |  name  | species | owner_id | id |     name     |   house    
----+--------+---------+----------+----+--------------+------------
  3 | Hedwig | owl     |        3 |  3 | Harry Potter | Gryffindor
  5 | unknown | owl     |        4 |  4 | Draco Malfoy | Slytherin
(2 rows)

Nice, it looks like we only have 2 rows. There is no wizard with an owner_id of 10 in the wizards table, so it's appropriate that Brodwin the owl is not present in our data.

1. Let's now just filter down to the columns we need:

postgres=#
SELECT wizard.name, pet.name FROM pet JOIN wizard ON wizard.id = pet.owner_id WHERE pet.species = 'owl';
     name     |  name  
--------------+--------
 Harry Potter | Hedwig
 Draco Malfoy | unknown
(2 rows)

We have the data we need -- let's step through this query.

1. SELECT wizard.name, pet.name : We want to select just two fields; the name of the pet, and the name of the wizard.
2. FROM pet : pet is our first table, or the table to the left
3. JOIN wizard : When we don't specify a type of join, it is assumed we want to run an inner join
4. ON wizard.id = pet.owner_id : The ON clause
5. WHERE pet.species = 'owl'; : We filter down to only pets that are owls

The INNER JOIN clause also comes with alternate syntax, which has slightly fewer words to type.
You could run:

SELECT wizard.name, pet.name
FROM pet, wizard
WHERE wizard.id = pet.owner_id
AND pet.species = 'owl'

Notice that the JOIN .. ON statement is absent. Instead, we specify both the tables in the FROM clause, and specify the condition with WHERE. Let's see this in action

postgres=#
SELECT wizard.name, pet.name FROM pet, wizard WHERE wizard.id = pet.owner_id AND pet.species = 'owl';
     name     |  name  
--------------+--------
 Harry Potter | Hedwig
 Draco Malfoy | unknown
(2 rows)

Nice, this returned the same data. We've learnt about the INNER JOIN!

1. You can find this github here
2. A cool article about the owls of harry potter can be found here
3. The schema for the pet and wizard tables can be found in the introduction to the series here. You should be able to follow along without any prior reading!