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:
- From the
pet
table, we want all the pets that are owls - 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:
Since we are only interested in data present in both tables, we will write an inner join. Let's write some SQL:
- First, let's get all pets that are owls:
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
- Let's write a JOIN:
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.
- Let's now just filter down to the columns we need:
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.
-
SELECT wizard.name, pet.name
: We want to select just two fields; the name of the pet, and the name of the wizard. -
FROM pet
: pet is our first table, or the table to the left -
JOIN wizard
: When we don't specify a type of join, it is assumed we want to run an inner join -
ON wizard.id = pet.owner_id
: The ON clause -
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:
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
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
!
Top comments (0)