DEV Community


Posted on

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:

id name house
1 Neville Longbottom Gryffindor
2 Ronald Weasley Gryffindor
3 Harry Potter Gryffindor
4 Draco Malfoy Slytherin
5 Seamus Finnigan Gryffindor
6 Hermione Granger Gryffindor

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 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.

id name species owner_id
2 Trevor toad 1
1 Scabbers rat 2
3 Hedwig owl 3
4 Crookshanks cat 6
5 unknown owl 4
6 Norbert Dragon 100
7 Brodwin owl 10

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

Top comments (0)