RIGHT and FULL JOIN

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — RIGHT JOIN: the mirror image

RIGHT JOIN is the mirror of LEFT JOIN: it keeps every row of the right table (the one after JOIN).

SELECT t.name AS trainer, c.name AS codemon FROM trainers t RIGHT JOIN codemon c ON c.trainer_id = t.trainer_id ORDER BY c.name;

Every Codémon is there, and Plantachu has NULL as trainer. This is exactly the result of FROM codemon c LEFT JOIN trainers t from the previous chapter — the same question asked from the other side.

Many people never write RIGHT JOIN

Since you can always swap the two tables and use LEFT JOIN, most developers only use LEFT JOIN and read queries from left to right. Know RIGHT JOIN exists, but don't feel obliged to use it.

Exercise 2 — FULL OUTER JOIN: nobody left behind

FULL OUTER JOIN keeps the rows of both tables: the matches, plus the unmatched rows of the left side, plus the unmatched rows of the right side.

SELECT t.name AS trainer, c.name AS codemon FROM trainers t FULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_id ORDER BY t.name, c.name;

Nine rows: the 7 real pairs, Jordan with no Codémon, and Plantachu with no trainer. Each unmatched row has NULL on the side where nothing matched.

Four joins, one picture

Think of the two circles: INNER = the overlap only, LEFT = the whole left circle, RIGHT = the whole right circle, FULL = both circles.

Exercise 3 — Count the full join

4 trainers (one owns nothing), 8 Codémons (one is wild), 7 real pairs. How many rows does this query return?

SELECT t.name, c.name FROM trainers t FULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_id;
Options
• 9
• 7
• 8
• 12

Hint : The matches, plus one row per unmatched trainer, plus one row per unmatched Codémon.

Exercise 4 — Protect the right side

Complete the query so that every Codémon appears, with its trainer when it has one, without swapping the two tables.

Hint : The Codémons are on the right of the JOIN keyword.

Exercise 5 — What each join keeps

Match each join with the rows it keeps.

Items to match
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
Definitions
• Only the rows that match on both sides
• All rows of the left table, matched or not
• All rows of the right table, matched or not
• All rows of both tables

Exercise 6 — Pick the join

You want exactly one row per Codémon, with the trainer's name when there is one, and you start the query with FROM trainers t. Which join do you need?

Options
• RIGHT JOIN codemon c ON c.trainer_id = t.trainer_id
• LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
• INNER JOIN codemon c ON c.trainer_id = t.trainer_id
• FULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_id

Hint : LEFT would lose Plantachu, FULL would add a Jordan row with no Codémon.

Exercise 7 — A friendlier NULL

Complete the query so that every Codémon is listed with its trainer's name, or the word wild when it has no trainer.

Hint : Keep every Codémon, then replace the missing trainer name with a fallback — you learned that function in the lesson on conditional logic and NULL functions.

Exercise 8 — The complete picture

The league wants one list with everybody: every trainer and every Codémon, paired when possible.

Write a query that returns trainer (the trainer's name, or nobody when a Codémon has no trainer) and codemon (the Codémon's name, or none when a trainer owns nothing), using a FULL OUTER JOIN. Sort by trainer, then codemon.

Starter code
-- Write your query below
Back to the course