Learn SQL: RIGHT / FULL JOIN
RIGHT and FULL JOIN
"There are also coaches who don't have a team yet," says Yasmine. "Lotte and Ruben started this season. I want one overview with everyone in it, partner or no partner."
RIGHT JOIN, the mirror image
RIGHT JOIN keeps every row of the right table, the table after JOIN. So here every coach stays, including Lotte and Ruben without a team.
It's the same as a LEFT JOIN with the tables swapped. That's why many people always write LEFT JOIN and put the most important table first.
SELECT t.name AS team, c.name AS coach FROM teams t RIGHT JOIN coaches c ON t.coach_id = c.id| team | coach |
|---|---|
| U11 | Ahmed El Amrani |
| U13 | Koen Peeters |
| Women A | Sofie Wouters |
| Men A | Marc Janssens |
| NULL | Lotte Claes |
| NULL | Ruben Smets |
FULL JOIN keeps everything
FULL JOIN (or FULL OUTER JOIN) keeps the rows of both sides. Teams without a coach and coaches without a team are all in it, each with NULL on the empty side.
Handy for seeing at a glance what doesn't belong anywhere.
SELECT t.name AS team, c.name AS coach FROM teams t FULL JOIN coaches c ON t.coach_id = c.id| team | coach |
|---|---|
| U11 | Ahmed El Amrani |
| U13 | Koen Peeters |
| U15 | NULL |
| Women A | Sofie Wouters |
| Men A | Marc Janssens |
| Veterans | NULL |
| NULL | Lotte Claes |
| NULL | Ruben Smets |
Try it yourself
Which coaches don't have a team yet? Show their names.
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