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:
- Left Joins
- Inner Joins
- 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:
-
id
: A unique id used to identify a wizarding student -
name
: The student's full name -
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:
-
id
: Unique id used to identify the pet -
name
: The name of the pet -
species
: The species of the pet. -
owner_id
: The id of the owner of the pet. In general,pet.owner_id
equalswizard.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.
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!
- View this on github
- Pottermore's guide to wizarding world pets
Top comments (0)