Who signed up and who did not?
One list holds all the pupils. Another holds club sign-ups. The club leader asks two things.
Who signed up? And which pupils have not signed up at all? Two different joins answer those two questions.
Key words
| Word | What it means |
|---|---|
| Join | matching rows from two tables by a shared value |
| Inner join | keeps only the rows that match in both tables |
| Outer join | keeps unmatched rows from one or both tables |
| Left join | keeps every row of the left table |
| Right join | keeps every row of the right table |
| NULL | a missing value |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can choose between an inner, left or right join to keep the rows I need.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
An inner join keeps matches only
An inner join keeps a row only when both tables have a match. Pupils with no sign-up vanish. Sign-ups with no pupil vanish too.
Use it when you want only the complete pairs.
Try it: inner join
select pupils.name, signups.club
from pupils
inner join signups on signups.pupil_id = pupils.pupil_id
order by pupils.name, signups.club;
-- Count the rows. Ada and Mia are missing. So is drama.
Outer joins keep the unmatched rows
An outer join keeps rows even when there is no match. The missing side is filled with NULL.
A left join keeps every row of the left table. A right join keeps every row of the right table. The left table is the one named first.
Try it: left join
select pupils.name, signups.club
from pupils
left join signups on signups.pupil_id = pupils.pupil_id
order by pupils.name, signups.club;
-- Ada and Mia now appear, with NULL for the club.
Try it: right join
select pupils.name, signups.club
from pupils
right join signups on signups.pupil_id = pupils.pupil_id
order by signups.signup_id;
-- Now every sign-up appears. Drama has no pupil, so name is NULL.