Learn SQL: LEFT JOIN
Forget no one with LEFT JOIN
"Where's the U15?" Yasmine points at the screen. "And the Veterans? They're not in the list of teams with their coach." They have no coach, so `JOIN` dropped them.
LEFT JOIN keeps the left table whole
LEFT JOIN keeps every row of the left table, the table before JOIN, even when there's no partner on the right. The columns from the right are then NULL.
Now the U15 and the Veterans do show up, with an empty coach.
SELECT t.name, c.name AS coach FROM teams t LEFT JOIN coaches c ON t.coach_id = c.id| name | coach |
|---|---|
| U11 | Ahmed El Amrani |
| U13 | Koen Peeters |
| U15 | NULL |
| Women A | Sofie Wouters |
| Men A | Marc Janssens |
| Veterans | NULL |
Finding what's missing
With LEFT JOIN you can also find what doesn't exist: rows that found nothing on the right have NULL there. Filter on IS NULL and only those remain.
This query looks for the teams that haven't played a single match yet.
SELECT t.name FROM teams t LEFT JOIN matches m ON m.team_id = t.id WHERE m.id IS NULL| name |
|---|
| Veterans |
Try it yourself
Which teams don't have a coach yet? Use a LEFT JOIN with coaches and show the team name.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson