SQL for the Potterhead: Inner Joins
Tags: sql
Published: 2020-02-19 05:01:35


#### 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: 
![Venn diagram for inner join](https://dev-to-uploads.s3.amazonaws.com/i/louk9qngsnoc9mi65xpg.png)

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: 

```sh 

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 

2. Let's write a JOIN:

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

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

```sh 
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: 
```sql 
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 

```sh 
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](https://github.com/kamyashethia/SQLForThePotterhead/blob/master/Joins.md) 
2. A cool article about the owls of harry potter can be found [here](https://harrypotter.fandom.com/wiki/Owl)
3. The schema for the `pet` and `wizard` tables can be found in the introduction to the series [here](https://dev.to/shethiakamya/sql-for-the-potterhead-joins-introduction-49h1). You should be able to follow along without any prior reading!