LEFT JOIN and the rows that are missing

An inner join hides your gaps

Twelve customers, and this query returns eleven names. Sable & Co has never ordered, so there is no row on the other side and nothing to glue them to. The report is not wrong, exactly — it answers a slightly different question from the one that was asked.

eleven rows, and a missing customer nobody notices

select   c.name, count(*) as orders
from     customers c
join     orders o on o.customer_id = c.id
group by c.id;

left join keeps the left-hand rows

A left join says: every row of the left table comes back, matched or not. Where there is no match, every column of the right table is NULL for that row. right join is the mirror image and is rarely written — swapping the tables reads better. full outer join keeps both sides (PostgreSQL and SQLite 3.39+; MySQL has no such thing).

twenty-five rows: the twenty-four orders, plus Sable & Co with nulls

select c.name, o.id, o.status
from   customers c
left join orders o on o.customer_id = c.id;

Counting with a left join: count the other table, not the rows

count(*) would give the customer with no orders a score of 1

select   c.name, count(o.id) as orders          -- count(*) → 1 for Sable & Co
from     customers c
left join orders o on o.customer_id = c.id
group by c.id, c.name
order by orders, c.name;                          -- Sable & Co 0

This is the classic left-join bug

count(*) counts the row that the left join invented, so "no orders" becomes "one order". count(o.id) counts only rows where the right side really matched — because count(column) skips NULL, exactly as the NULL lesson said.

The where clause that quietly undoes it

Any where condition on the right-hand table — except is null — removes the unmatched rows, because NULL fails every comparison. If a filter is part of what counts as a match, it goes in on. If it is a question about the result, where is right and the join should probably be inner.

the same two words in two places, two different queries

-- broken: the filter throws away the invented NULL row, turning this back into an inner join
select c.name, o.id
from   customers c
left join orders o on o.customer_id = c.id
where  o.status = 'fitted';

-- right: the condition belongs to the match, so unmatched customers survive
select c.name, o.id
from   customers c
left join orders o on o.customer_id = c.id and o.status = 'fitted';

The anti-join: finding what is missing

left join … where right.key is null is the anti-join, and it is worth memorising as one shape. not exists says the same thing and is usually as fast; not in says it too and breaks on NULLs, as the NULL lesson showed.

three questions this database can answer

-- a customer who has never ordered            → Sable & Co
select c.name from customers c
left join orders o on o.customer_id = c.id
where  o.id is null;

-- a door nobody has ever bought                → Noir
select d.name from doors d
left join order_items i on i.door_id = d.id
where  i.id is null;

-- a fitter with no work                        → Mira Qasimova
select f.name from fitters f
left join jobs j on j.fitter_id = f.id
where  j.id is null;

Rows on both sides that are NULL

Here the NULL is in the data, not invented by the join: four jobs have fitter_id NULL. on f.id = j.fitter_id is unknown for those rows, so they never match — and the left join keeps them anyway. Both kinds of NULL end up looking identical in the result, which is why coalesce in the select list is such a habit.

twenty jobs, four with nobody assigned

select j.day, j.hours, coalesce(f.name, 'unassigned') as fitter
from   jobs j
left join fitters f on f.id = j.fitter_id
order by j.day;