Level 10: LEFT JOIN and multiple joins

Lesson

JOIN makes the rows without a match disappear. Yet those are often the ones that matter: the customers who never ordered, the books never borrowed. LEFT JOIN keeps them.

LEFT JOIN keeps all the rows of the left table, the one written in the FROM. When a row has a match, you get the same thing as with JOIN. When it has none, it is kept anyway, and all the columns of the right table are NULL. FROM directors d LEFT JOIN movies m … thus keeps Sara Diaz, with a NULL title.

To find the “orphans”, you do a LEFT JOIN then WHERE right.id IS NULL: only the left rows without a match remain. To count, use COUNT(right.id), which is 0 for a row without a match; COUNT(*) would count 1, because the row does exist in the result.

Classic trap: a condition on the right table placed in the WHERE. The WHERE runs after the join; for a row without a match, the right column is NULL, the test fails and the row disappears, just as with an ordinary JOIN. To filter the right table without losing the left rows, put the condition in the ON: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'. Challenge 10.5 makes you meet this trap.

You can chain as many joins as needed, each with its own ON. A table can also be joined to itself: this is a self-join. In staff, manager_id points to the id of another employee. FROM staff e LEFT JOIN staff m ON m.id = e.manager_id reads the table twice under two aliases: e for the employee, m for their manager. m.name then gives the manager's name, and the LEFT JOIN keeps Alice, who has none.

Syntax

SELECT …
FROM a
LEFT JOIN b ON b.a_id = a.id
JOIN c ON c.id = b.c_id;

Worked example

SELECT d.name, m.title
FROM directors d
LEFT JOIN movies m ON m.director_id = d.id;

Every director is listed. Sara Diaz, who has no movie, appears with an empty title (NULL).

Example result

Key points

• LEFT JOIN keeps the whole left table.
• LEFT JOIN + IS NULL finds the “orphans”.
• A self-join uses two aliases for the same table.

Common pitfalls

• A WHERE on a column of the right table can cancel the effect of the LEFT JOIN.
• COUNT(*) counts 1 even when there is no match: use COUNT(right_column).

Going further

RIGHT JOIN keeps the whole right table and FULL JOIN keeps both sides (available in SQLite since version 3.39). CROSS JOIN pairs every row with every row of the other table: this is the Cartesian product, useful to generate all combinations.

The level’s 5 exercises

1. Guided · Show the name of each artist and their number of songs, including the artists who have none (0). (Tables: artists, songs)
2. Practice · Show the name of the members who never borrowed a book. (Tables: members, loans)
3. Practice · For each loan, show the member's name, the book title and the loan date. (Tables: loans, members, books)
4. Practice · Show the name of each employee and the name of their manager (empty for the person who has none). (Table: staff)
5. Challenge · For each customer, show their name and the number of their orders not shipped, that is whose status is different from 'shipped' (not_shipped). Customers with none must appear with 0. (Tables: customers, orders)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic