SQL for the Potterhead: Joins Introduction

Querying data with SQL can feel magical, and JOINS are one of the things that feel the most magical to me. In the following article, I'm going to explain the magic behind joins.

We will cover:

1. Left Joins
2. Inner Joins
3. Outer Joins

Let's start with some setup and talk about the pets of the wizarding world. Students at Hogwarts are allowed to bring with them an owl OR a cat OR a toad.

Let's consider a table to hold some of the students at Hogwarts. The table will have the following schema:

1. id : A unique id used to identify a wizarding student
2. name : The student's full name
3. house : Their Hogwarts house

Here's the table, populated with some students:

Now, let's create a table to hold information about the student's pets. The table will have the following schema:

1. id: Unique id used to identify the pet
2. name : The name of the pet
3. species: The species of the pet.
4. owner_id: The id of the owner of the pet. In general, pet.owner_id equals wizard.id. In database-ey terms, we think of this as a foreign key. (We will not be explicitly specifying a foreign key relation here)

Here is a table with some pets.

Let's confirm our understanding of this table is correct, by looking at the pet with id = 3. The pet's name is Hedwig, and it's owner_id is 3. Looking at the wizard table, Harry Potter has an id of 3. The data indicates that Harry Potter owns Hedwig, which is what we would expect.

Now that we have table schemas ready, let's learn about joins!

1. View this on github
2. Pottermore's guide to wizarding world pets