Joins: reason about them, do not memorise them

Forget the Venn diagrams

The circles do not tell you what actually happens, which is this:

For each row on the left, the database looks for rows on the right where the ON condition is true, and emits one output row per match.

Everything follows from that one sentence. In particular, two questions decide the behaviour of any join you will ever write:

1. 1What happens to a left row with zero matches?
2. 2What happens to a left row with two matches?

An inner join drops the zero-match row. A left join keeps it and fills the right-hand columns with NULL. That is the entire difference between them.

And for two matches, both kinds of join do the same thing: they emit two rows. The left row is duplicated. This is not a bug and there is no flag to turn it off. It is what a join is.

Row multiplication, which is where the money goes wrong

SELECT c.name, c.credit_limit, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

If Mariana has three orders, Mariana appears three times, and her credit limit appears three times with her. Now watch this go wrong:

SELECT SUM(c.credit_limit)
FROM customers c
JOIN orders o ON o.customer_id = c.id;

The total credit limit is now roughly the number of orders times the average limit. It is enormous. It is also completely plausible-looking, which is the dangerous part. Nothing errors. You get a number.

The defence is a sentence you say before you write the query: "one row of my result is ______."

• "One row per order, with the customer's city attached." Each order has exactly one customer, so at most one match. Joining is safe.
• "One row per customer, with their total order value." That is one row per customer, but the join produces one row per order. So the join has to be followed by a grouping, or the total belongs in a subquery.

If you cannot finish that sentence, you are not ready to write the join.

The trap that turns a LEFT JOIN into an inner join

This is the single most common join bug in working code.

SELECT o.id, r.reason
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE r.reason <> 'damaged';

The intent is "all orders, with refund reason where there is one, excluding damage refunds." What you get is only orders that have a refund.

Why: the LEFT JOIN faithfully keeps the unmatched orders and sets r.reason to NULL for them. Then WHERE asks whether NULL is different from 'damaged', and the answer is not true — it is unknown. WHERE keeps only rows where the test is true. Every unmatched order is discarded a step after the join carefully preserved it.

Two fixes, and they mean different things:

-- Filter the right-hand side before matching: keeps all orders,
-- and simply never attaches a damage refund to them.
LEFT JOIN refunds r ON r.order_id = o.id AND r.reason <> 'damaged'

-- Filter after matching, but spare the unmatched rows explicitly.
WHERE (r.reason <> 'damaged' OR r.id IS NULL)

General rule: a condition on the right-hand table of a LEFT JOIN belongs in ON. A condition on the left-hand table belongs in WHERE.

The anti-join, which is worth knowing by name

"Orders that have no refund at all":

SELECT o.id
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE r.id IS NULL;

Join everything, then keep only the rows where the right side came back empty. Read it as "look for a match, and keep the failures." It is the standard way to ask "which of these has none of those", and once you have seen it you will use it constantly: students with no payment this month, products never sold, users who never opened the app.

The reasoning habit

When a join surprises you, do not reach for a different join type and hope. Count. Run SELECT COUNT(*) on the left table alone, then on the join. If the join returned more rows than the left table has, something on the right matched more than once, and you have found your bug. If it returned fewer, an inner join or a WHERE clause is dropping rows you meant to keep.