SQL LEFT JOIN explained with examples

LEFT JOIN and the missing matches

An INNER JOIN throws away anything without a partner. Finn has no club, so Finn disappears. Sometimes that is exactly the bug in your report: "why does the school only have 7 students?"

A LEFT JOIN keeps every row from the left table (the one after FROM), matched or not:

SELECT s.name, c.name AS club
FROM students AS s
LEFT JOIN clubs AS c ON s.club_id = c.id;

Students with a club get their club name as usual. Students with no match still appear, and the columns from the right table are filled with NULL, because there is nothing to put there.

Which table is "left" matters. Flip it around and you get every club, with its members where they exist and NULLs for a club nobody joined:

SELECT c.name AS club, s.name AS student
FROM clubs AS c
LEFT JOIN students AS s ON s.club_id = c.id;

Now the fun trick. Those NULLs mark exactly the rows that found no partner. Filter for them, and you get "things with no match":

SELECT c.name
FROM clubs AS c
LEFT JOIN students AS s ON s.club_id = c.id
WHERE s.id IS NULL;

That is every club with zero members. This pattern answers loads of real questions: customers who never ordered, songs nobody played, homework nobody handed in. Remember from the NULL lesson that it has to be IS NULL, not = NULL.

Also handy: COUNT(s.id) after a LEFT JOIN with GROUP BY gives 0 for empty clubs, because COUNT(column) skips NULLs. COUNT(*) would say 1, counting the lonely NULL row. Small difference, very wrong answer.

Tables in this lesson

clubs 6 rows

id	name	room
1	Robotics	B12
2	Chess	A03
3	Drama	Hall
4	Esports	B12
5	Knitting	A07
6	Beekeeping	Garden

students 9 rows

id	name	year	club_id
1	Aisha	10	2
2	Ben	11	3
3	Chloe	10	1
4	Dev	12	2
5	Ella	11	1
6	Finn	10	NULL
7	Grace	12	4
8	Hugo	11	4
9	Ivy	12	NULL

Show the SQL that built them

CREATE TABLE clubs (id INTEGER PRIMARY KEY, name TEXT, room TEXT);
INSERT INTO clubs VALUES
 (1, 'Robotics', 'B12'),
 (2, 'Chess', 'A03'),
 (3, 'Drama', 'Hall'),
 (4, 'Esports', 'B12'),
 (5, 'Knitting', 'A07'),
 (6, 'Beekeeping', 'Garden');
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, year INTEGER, club_id INTEGER);
INSERT INTO students VALUES
 (1, 'Aisha', 10, 2),
 (2, 'Ben', 11, 3),
 (3, 'Chloe', 10, 1),
 (4, 'Dev', 12, 2),
 (5, 'Ella', 11, 1),
 (6, 'Finn', 10, NULL),
 (7, 'Grace', 12, 4),
 (8, 'Hugo', 11, 4),
 (9, 'Ivy', 12, NULL);

Try it yourself

Edit it. Break it. Run it again.

-- Every student, club or no club
SELECT s.name, c.name AS club
FROM students AS s
LEFT JOIN clubs AS c ON s.club_id = c.id;

Your turn

Type it yourself. That is the whole trick.

1Lonely clubs

Return the name of every club that has no students at all.

SELECT c.name
FROM clubs AS c
INNER JOIN students AS s ON s.club_id = c.id;

2Members per club

Return every club's name and how many students are in it, including clubs with 0 members.

SELECT c.name, COUNT(*)
FROM clubs AS c
INNER JOIN students AS s ON s.club_id = c.id
GROUP BY c.id;