Starts this lesson and continues through 3 more to the end of the course.

Joins and the fan-out problem

The failure this lesson prevents

Revenue is 3× too high. Nothing errored. The query is four lines long and looks perfect. The join condition is correct. And the number is wrong.

This is fan-out, and it is the most expensive bug in analytics engineering, because it produces a number that is believable. A crash gets fixed in an hour. A revenue figure that's 3% high because one dimension table has duplicate keys can sit in a board pack for two years.

Joins are cardinality arithmetic

A join is not "looking up a value". A join is a filtered cross product, and its output row count is determined by the multiplicity on each side:

Left : Right	Output rows	Safe?
1 : 1	same as left	✅
Many : 1	same as left	✅ — the lookup case
1 : Many	multiplied	⚠️ intended sometimes
Many : Many	multiplied, badly	🚨 almost always a bug

The rule to hold: a join to the "one" side of a many-to-one relationship is safe. Anything else changes your row count, and therefore every SUM downstream.

-- 1,000 orders. Looks like a harmless enrichment.
SELECT SUM(o.amount)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

If customers has exactly one row per customer_id, this is correct. If a botched load inserted one customer twice, every order for that customer now appears twice, and SUM(o.amount) double-counts those orders. No error. No warning. Just a bigger number.

⚠️ The dangerous property is that fan-out is invisible in the output you look at. You look at one aggregate figure. The duplication happened in rows you never displayed.

Prove it rather than assume it

Three checks. Do them before trusting a join, not after someone queries the number.

1. Is the join key actually unique on the "one" side?

SELECT customer_id, COUNT(*) AS n
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

Empty result = safe. This takes ten seconds and it is the single highest-value habit in this module.

2. Did the row count change?

SELECT
  (SELECT COUNT(*) FROM orders)                                          AS before_join,
  (SELECT COUNT(*) FROM orders o
     JOIN customers c ON c.customer_id = o.customer_id)                  AS after_join;

For a many-to-one join these must be equal, unless you intended to drop unmatched rows — in which case after_join should be smaller, never larger. Larger means fan-out.

3. Does the aggregate survive de-duplication?

-- If these disagree, the join multiplied rows.
SELECT SUM(amount) FROM orders;                                    -- ground truth
SELECT SUM(o.amount) FROM orders o JOIN customers c USING (customer_id);

Fixing fan-out

In rough order of preference:

1. Fix the data. If customers should have one row per customer and doesn't, that's an upstream defect. Everything below is a workaround.

2. Aggregate before joining. The cleanest fix when the "many" side genuinely has many rows:

-- WRONG: joining orders to order_items multiplies orders by item count
SELECT o.order_id, SUM(o.amount)        -- o.amount now counted once per item
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.order_id;

-- RIGHT: collapse the many side first, then join 1:1
SELECT o.order_id, o.amount, i.item_count
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.order_id;

3. De-duplicate the dimension explicitly, if you can't fix upstream — with a documented rule for which row wins (lesson 4 covers doing this correctly).

4. EXISTS instead of a join, when you only need to test membership and don't need columns:

-- Cannot fan out. Cannot change your row count. Ever.
SELECT SUM(o.amount)
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

⚠️ This is the underused one. If you're joining purely to filter, EXISTS is both safer and clearer about intent. A join says "I want columns from there"; EXISTS says "I want to know if it's there". Say what you mean.

The multiple-fact-table trap

The nastiest fan-out has no duplicate keys anywhere. Every table is clean.

-- orders: 100 rows.  shipments: 150 rows.  returns: 20 rows.  All keys valid.
SELECT o.order_id, SUM(s.weight), SUM(r.refund)
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id
LEFT JOIN returns   r ON r.order_id = o.order_id
GROUP BY o.order_id;

An order with 3 shipments and 2 returns produces 6 rows — every shipment paired with every return. SUM(s.weight) counts each shipment twice, SUM(r.refund) counts each return three times. Both aggregates are wrong, by different factors, in the same query.

You cannot join two independent one-to-many relationships to the same parent and aggregate both. The fix is to aggregate each branch separately first, then join the results:

SELECT o.order_id, s.total_weight, r.total_refund
FROM orders o
LEFT JOIN (SELECT order_id, SUM(weight) AS total_weight FROM shipments GROUP BY order_id) s
       ON s.order_id = o.order_id
LEFT JOIN (SELECT order_id, SUM(refund) AS total_refund FROM returns   GROUP BY order_id) r
       ON r.order_id = o.order_id;

This is sometimes called a chasm trap. Knowing the name is worth an interview point; knowing the fix is worth the job.

`LEFT JOIN` semantics people get wrong

A predicate on the right table in WHERE turns a LEFT JOIN into an inner join.

-- Silently an INNER JOIN: unmatched rows have s.status = NULL,
-- and NULL = 'delivered' is unknown, so they're dropped (lesson 1).
SELECT o.*, s.status
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id
WHERE s.status = 'delivered';

-- Actually a LEFT JOIN: filter in the ON clause instead.
SELECT o.*, s.status
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id AND s.status = 'delivered';

The rule: ON filters what gets joined; WHERE filters what survives. For the preserved side of an outer join, that distinction changes the result. This is a top-five interview question and the explanation above — "unmatched rows are NULL, and NULL = 'delivered' is unknown" — is the answer they're listening for, because it ties back to three-valued logic.

And COUNT on a LEFT JOIN:

-- WRONG: counts 1 for orders with no items, because COUNT(*) counts the row
SELECT o.order_id, COUNT(*) FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id GROUP BY o.order_id;

-- RIGHT: COUNT(expression) ignores NULLs, so unmatched orders count 0
SELECT o.order_id, COUNT(i.item_id) FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id GROUP BY o.order_id;

That follows directly from the documented behaviour — count(*) "computes the number of input rows", while count(expression) "computes the number of input rows in which the input value is not null" (PostgreSQL: Aggregate Functions, verified 2026-08-12). Lesson 3 goes deeper.

Check yourself

Revenue is exactly 2× expected after adding a customer join. What's your first query?
When is EXISTS strictly better than a join?
Why does putting s.status = 'delivered' in WHERE break a LEFT JOIN?
orders joined to both shipments and returns, all keys valid, both SUMs wrong. Name it and fix it.
COUNT(*) vs COUNT(i.item_id) on a LEFT JOIN — which gives 0 for unmatched rows?

Answers

Check uniqueness of the join key on the dimension: SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) > 1. Exactly 2× strongly suggests every customer is duplicated once.
When you only need to test membership and don't need columns from the other table. EXISTS cannot change your row count, so it cannot fan out — and it states the intent.
Unmatched rows have NULL on the right side. NULL = 'delivered' is unknown, not true, so WHERE drops them — which is precisely what an inner join does. Put the predicate in ON instead.
A chasm trap: two independent one-to-many branches joined to the same parent produce the cross product of the two. Aggregate each branch in its own subquery, then join those.
COUNT(i.item_id). COUNT(*) counts rows, and an unmatched left-joined row still exists, so it returns 1.

Teaching this section

Do the arithmetic on the board, not the SQL. Three shipments × two returns = six rows. People who can't read a query plan can all multiply, and once they see 3 × 2 they never forget the chasm trap.
Demo: run the row-count-before/after check live. Make it look like a two-second habit, because it is, and that's the point.
Good discussion: "Which of our dimension tables do we actually know are unique?" Then have them run the HAVING COUNT(*) > 1 check on three of them during the session. Something usually turns up.
Watch for: people reaching for SELECT DISTINCT to fix fan-out. It hides the symptom, changes the aggregate in ways they haven't reasoned about, and leaves the real duplicate in place. Call it out every time.
Timing: 25 min presented, 15 hands-on.

← PreviousNULL and three-valued logic Next →Counting things correctly