Nobody Left Out

The row that disappears

An inner join builds only the pairs that match. If a row on one side has no partner on the other, it contributes nothing to the result and nothing tells you it was ever there.

CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, keeper_id INTEGER);
INSERT INTO keepers (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO hives (label, keeper_id) VALUES ('north hedge', 1), ('orchard', 1), ('lane end', 2);

SELECT k.name, h.label
FROM keepers AS k
JOIN hives AS h ON h.keeper_id = k.id
ORDER BY k.name;

name  | label
------+------------
Asha  | north hedge
Asha  | orchard
Bruno | lane end
(3 rows)

Three keepers, three rows — and they are not the same three. Cleo keeps no hives, so she is absent, while Asha appears twice because she keeps two. Hand that table over as "our keepers" and you have quietly lost a member of the association.

LEFT JOIN keeps her

LEFT JOIN keeps every row from the table on the left of the join, matched or not, and fills in the right-hand table's columns with NULL where there was no match.

CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, keeper_id INTEGER);
INSERT INTO keepers (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO hives (label, keeper_id) VALUES ('north hedge', 1), ('orchard', 1), ('lane end', 2);

SELECT k.name, h.label
FROM keepers AS k
LEFT JOIN hives AS h ON h.keeper_id = k.id
ORDER BY k.name, h.label;

name  | label
------+------------
Asha  | north hedge
Asha  | orchard
Bruno | lane end
Cleo  | NULL
(4 rows)

Four rows now, and the fourth is Cleo with a NULL label. That NULL does not exist in either table — the join made it, to have something to put in the column.

The counting trap

Which matters as soon as you count. count(*) counts rows in the group, and Cleo's group has one row, because the join gave her one. count(h.id) counts values that are not NULL, and hers is NULL.

CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, keeper_id INTEGER);
INSERT INTO keepers (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO hives (label, keeper_id) VALUES ('north hedge', 1), ('orchard', 1), ('lane end', 2);

SELECT
  k.name,
  count(*)      AS wrong,
  count(h.id)   AS right_answer
FROM keepers AS k
LEFT JOIN hives AS h ON h.keeper_id = k.id
GROUP BY k.id, k.name
ORDER BY k.name;

name  | wrong | right_answer
------+-------+-------------
Asha  |     2 |            2
Bruno |     1 |            1
Cleo  |     1 |            0
(3 rows)

Cleo has one hive according to one column and none according to the other. Both numbers came from the same query, and only the right-hand one is true.

The rule is short enough to memorise: with a LEFT JOIN, count a column from the right-hand table, never *. Pick one that can never genuinely be NULL — the right table's primary key is the safe choice — so a row that matched but happens to have a blank column is not miscounted as a miss.

One more use for that NULL

The manufactured NULL is also how you find the rows that failed to match at all:

CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, keeper_id INTEGER);
INSERT INTO keepers (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO hives (label, keeper_id) VALUES ('north hedge', 1), ('orchard', 1), ('lane end', 2);

SELECT k.name AS keeps_no_hives
FROM keepers AS k
LEFT JOIN hives AS h ON h.keeper_id = k.id
WHERE h.id IS NULL;

keeps_no_hives
--------------
Cleo
(1 row)

LEFT JOIN then WHERE right.key IS NULL is the standard way to ask "what is in A but not in B". Note that any other WHERE condition on a right-hand column would have turned the LEFT JOIN back into an inner join, because a NULL fails every ordinary comparison.

Your turn

The editor holds four members, five loans, and a query that is meant to be every member with their loan count, busiest first.

Press Run first. Three rows come back for four members: Devi has never borrowed anything and has been dropped.

Two marked lines need changing:

1. The join, so that every member survives whether or not they have a loan.
2. The count, so that the member with no loans gets 0 rather than 1.

Run it again and you should see four rows, ending with Devi on zero.

Then make only the join change and look at Devi's number. That is the version that ships to production.

If something goes wrong

If you get four rows with Devi on 1, only the join was changed — the count is still counting rows.

If you get three rows, the join is still an inner JOIN.

If you get no such column: l.id, the alias on the joined table has gone missing; the line needs to read LEFT JOIN loans AS l.

Nothing here can break. Both tables are rebuilt from scratch on every run.