SQL joins

The tables below

Eight objects and five sources. Six of the objects name a source; two have source_id NULL, because nobody recorded where they came from. One source, Delacroix Freres, is on file but has no objects against it yet.

Those three facts are the only reason the four joins behave differently. In a tidy table where everything matches, all four return the same rows and you learn nothing — which is why the examples in most tutorials feel interchangeable.

SELECT * FROM objects;
-- then
SELECT * FROM sources;

INNER JOIN — only the matches

An inner join returns a row only where the condition finds something on both sides. Run it and count: six rows, not eight. The Bronze Hare and the Terracotta Figurine have vanished, and nothing on screen tells you they existed.

That silence is the real risk with INNER. It is the correct join far more often than not, but it is also how a report quietly loses records — and it will never warn you, because from the query's point of view nothing went wrong.

SELECT o.title, s.name AS source
FROM objects o
INNER JOIN sources s ON o.source_id = s.id
ORDER BY o.title;

LEFT JOIN — keep everything on the left

A left join keeps every row from the first table whether or not it matched, filling the other side with NULL where it did not. Eight rows this time: the Bronze Hare and the Terracotta Figurine are back, with a blank source.

This is the join to reach for by default when the left table is the thing you are reporting on. "Every object, with its source where we know it" is a left join. "Every object that has a source" is an inner join. Deciding which sentence you actually mean is most of the work.

SELECT o.title, s.name AS source
FROM objects o
LEFT JOIN sources s ON o.source_id = s.id
ORDER BY o.title;

RIGHT and FULL

RIGHT JOIN is LEFT JOIN with the tables the other way round: seven rows here, because Delacroix Freres appears with a blank title. You will see it rarely — most people swap the table order and write LEFT, which is the same query and easier to read at a glance.

FULL JOIN keeps unmatched rows from both sides: nine rows, the union of the two previous answers. It is the right tool when neither table is authoritative and you are reconciling them — comparing two systems that are supposed to agree, for instance.

-- 7 rows: every source, matched or not
SELECT o.title, s.name AS source
FROM objects o
RIGHT JOIN sources s ON o.source_id = s.id
ORDER BY s.name;

-- 9 rows: nothing dropped from either side
SELECT o.title, s.name AS source
FROM objects o
FULL JOIN sources s ON o.source_id = s.id
ORDER BY s.name, o.title;

The anti-join: finding what is missing

A LEFT JOIN followed by a test for NULL on the right-hand side returns exactly the rows that did not match. This pattern has a name — the anti-join — and it is the single most useful thing joins do that is not obvious from the diagrams.

Which sources have never given us anything? Which objects have no recorded provenance? Which invoice has no payment against it? Same shape every time: join, then keep the NULLs.

Test the right table's own key column, not the column you joined on. Testing s.id is unambiguous; testing something nullable in the source data would confuse "no match" with "matched, but the value was blank".

-- sources that have given us nothing: Delacroix Freres
SELECT s.name
FROM sources s
LEFT JOIN objects o ON o.source_id = s.id
WHERE o.id IS NULL;

-- objects with no recorded source: two of them
SELECT o.title
FROM objects o
LEFT JOIN sources s ON o.source_id = s.id
WHERE s.id IS NULL;

The mistake that turns your LEFT JOIN into an INNER one

Add a WHERE clause about the right-hand table and your left join silently stops being one. Run the first query below: four rows, not eight. The two unmatched objects are gone again.

The reason is order. The join runs first and produces eight rows, two of them with NULL in every source column. Then WHERE s.country = 'United Kingdom' tests those NULLs, gets unknown rather than true, and drops them. You wrote LEFT JOIN and got an inner join's behaviour.

Putting the same condition in the ON clause instead keeps all eight rows and simply declines to match the ones that fail it. Read the two results side by side — the difference between filtering after joining and filtering while joining is one of the few genuinely subtle things in everyday SQL.

-- 4 rows: the WHERE killed the outer join
SELECT o.title, s.name
FROM objects o
LEFT JOIN sources s ON o.source_id = s.id
WHERE s.country = 'United Kingdom';

-- 8 rows: the condition is part of the match, not a filter after it
SELECT o.title, s.name
FROM objects o
LEFT JOIN sources s ON o.source_id = s.id
                   AND s.country = 'United Kingdom'
ORDER BY o.title;

The other mistake: more rows than you started with

A join does not return "one row per row of the left table". It returns one row per matching pair. If a source had three objects and you joined from sources, that source appears three times — and any SUM you run over the result triples its value.

This is called fan-out, and it is the most common cause of a total that is wrong in a way nobody notices for months. The check is cheap: count the rows before and after the join. If the number went up, decide whether you meant it.

When you did not mean it, the fix is usually to aggregate the many side first and join the summary, rather than joining the detail and trying to un-double it afterwards.

ON versus USING, and table aliases

USING (source_id) is shorthand for ON a.source_id = b.source_id and works only when both columns share a name. It is tidier when they do, and it collapses the two columns into one in the output.

The one-letter aliases in these examples — o and s — are not decoration. Once two tables are in play, an unqualified column name is ambiguous to the reader even when the database can resolve it, and the query that made sense when you wrote it is the one you will be reading in six months.