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:
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,
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!