Joins that do not fan out

You already know the join types. This lesson is about the failure that survives knowing them: a join that is syntactically fine, returns no error, and silently multiplies your measures.

Fan-out, demonstrated#

Orders, one row each:

Order tags, several rows each:

select sum(o.revenue)
from orders o
join order_tags t on t.order_id = o.order_id;

The answer is 400, not 300. O1's revenue was counted once per tag. Nothing warned you, the query is idiomatic, and the number is plausible — which is exactly why it survives review.

Three ways to fix it#

Aggregate the many side first. Usually the right answer.

select sum(o.revenue)
from orders o
left join (
    select order_id, string_agg(tag, ',') as tags, count(*) as tag_count
    from order_tags group by order_id
) t on t.order_id = o.order_id;

Filter the many side to one row. When you only want one.

left join (
    select distinct on (order_id) order_id, tag
    from order_tags order by order_id, priority desc
) t on t.order_id = o.order_id

Use a semi-join when you only need existence, not columns. exists never multiplies rows, which is its whole advantage over a join here.

select sum(o.revenue)
from orders o
where exists (select 1 from order_tags t where t.order_id = o.order_id and t.tag = 'gift');

Prove the grain before you trust the join#

Two checks, both fast, both worth making a habit:

-- 1. Is the join key actually unique on the side I think is "one"?
select order_id, count(*) from order_tags group by 1 having count(*) > 1 limit 5;

-- 2. Did the row count change when it should not have?
select count(*) from orders;                          -- 7000
select count(*) from orders o join dim x on ...;      -- must still be 7000

If the second number is larger, stop. Do not add distinct. distinct will make the row count look right while every sum() remains wrong, and it will hide the bug for months.

Left joins and the WHERE clause#

This is the other classic. A predicate on the right-hand table in the WHERE clause silently converts a left join into an inner join, because NULL = 'gift' is not true.

-- Silently an inner join. Orders with no tags vanish.
select o.*, t.tag from orders o
left join order_tags t on t.order_id = o.order_id
where t.tag = 'gift';

-- What was meant: filter the right side, keep all left rows.
select o.*, t.tag from orders o
left join order_tags t on t.order_id = o.order_id and t.tag = 'gift';

The rule: filters on the right-hand table of a left join belong in ON, not WHERE. Filters on the left table belong in WHERE. A WHERE t.something is null is the deliberate exception — that is the anti-join idiom, and it is fine because it is intentional.

NULL does not behave the way you expect#

Three-valued logic catches everyone at least once.

select null = null;            -- null, not true
select null <> 'a';            -- null, not true
select 1 in (2, null);         -- null → treated as not matching
select 1 not in (2, null);     -- null → NEVER returns rows. This one hurts.

That last line is worth memorising. not in against a subquery containing a single NULL returns zero rows, always, silently. Use not exists instead, which handles NULLs correctly:

select * from orders o
where not exists (select 1 from refunds r where r.order_id = o.order_id);

And for comparisons where NULLs should count as different, is distinct from gives you the intuitive behaviour:

where old.city is distinct from new.city   -- true when one side is null

Aggregates ignore NULLs, which is usually helpful and occasionally a trap: avg(x) divides by the count of non-null values, so a column that is 60% null gives you the average of the 40% that is present. If missingness is not random — and lesson four of the statistics module argues it usually is not — that average is biased.

Anti-joins and set operations#

Anti-join — rows in A with no match in B. not exists is clearest and safest.

Semi-join — rows in A with at least one match in B, without duplication. exists.

Set operators — union deduplicates and therefore sorts, which is expensive; union all does not. Use union all unless you specifically need deduplication, and if you do need it, ask why duplicates exist.

A join checklist#

Before you ship a query with joins:

1. What is the grain of my result? One sentence.
2. For each join: which side is one, which is many?
3. Did my row count change unexpectedly?
4. Are any left-join filters sitting in WHERE by mistake?
5. Is there a not in anywhere near a nullable column?