BrevFeed
Sign in

20. Join fan-out: diagnosing row-multiplication bugs

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

The bug that doesn't look like a bug

This is arguably the single most common real-world SQL mistake, and it's insidious precisely because it doesn't error. The query runs, returns a result, the result even looks plausible — it's just wrong, usually inflated, and you only notice when a total looks suspiciously large or a downstream report doesn't reconcile with a known-good number.

What "fan-out" actually is

A join multiplies rows whenever the join key isn't unique on at least one side. If a customer has 5 rentals, joining customer to rental on customer_id produces 5 rows for that customer — correctly, since each row represents a genuine customer-rental pairing. But if you then try to compute something about the customer (like "how many customers do we have") from that joined result without accounting for the multiplication, you'll overcount.

-- WRONG: this "counts customers" but actually counts customer-rental
-- PAIRS — a customer with 10 rentals gets counted 10 times.
SELECT count(*) AS customer_count
FROM customer c
JOIN rental r ON r.customer_id = c.customer_id;
-- Returns ~16000-ish (the rental count), not 599 (the actual customer count).

The join itself did exactly what it was asked — this isn't a join bug, it's a mismatch between the grain of the join result and the grain of the question being asked. "How many customers" is a question about the customer grain (one row per customer); the joined result is at the rental grain (one row per rental). Counting rows of a rental-grain result never answers a customer-grain question correctly.

The fix: count distinct, or don't join at all

-- Correct: distinct customer_id, at the grain the question actually asks.
SELECT count(DISTINCT c.customer_id) AS customer_count
FROM customer c
JOIN rental r ON r.customer_id = c.customer_id;
-- Returns 599 (or fewer, if some customers had zero rentals and got
-- dropped by the INNER JOIN — a second, related trap).

-- Better still, if you didn't need any rental column: don't join at all.
SELECT count(*) FROM customer;

That last line is worth sitting with — a wrong-row-count bug is often a sign the query joined a table it didn't actually need data from. If you never reference a single column from the joined table outside of an aggregate, ask whether the join (and the fan-out risk it introduces) was necessary at all.

A second flavor: SUM inflated by a join

Same root cause, more dangerous because the wrong number looks even more plausible — a total that's simply too large, easy to mistake for "yeah that sounds about right":

-- WRONG: if a customer has multiple rentals, their address row gets
-- joined in once per rental — but address doesn't multiply the SPEND,
-- so this specific example is actually safe. The real danger case is
-- below, joining TWO tables that both have a one-to-many relationship
-- to the base table.

-- WRONG: film joined to BOTH film_actor and inventory — a film with 5
-- actors AND 3 inventory copies produces 5*3=15 rows for that one film,
-- and a naive SUM/COUNT over that joined result is inflated 15x for it.
SELECT f.title, count(*) AS suspicious_count
FROM film f
JOIN film_actor fa ON fa.film_id = f.film_id
JOIN inventory i ON i.film_id = f.film_id
GROUP BY f.title
ORDER BY suspicious_count DESC
LIMIT 5;

This double-fan-out pattern — joining two independent one-to-many relationships off the same base table in a single query — is a particularly common way to accidentally multiply, not just duplicate. The fix is almost always to separate the two aggregations (count actors and count inventory in two independent subqueries or CTEs, Module 5) rather than trying to compute both from one joined-and-multiplied result.

The diagnostic habit

Before trusting a count(*)/SUM(...) after any join, ask: what's the grain of this joined result, and does it match the grain of the number I'm computing? Concretely:

  1. Identify the "one" side and the "many" side of each join in the query (Module 8 formalizes this as cardinality — for now, just: does one row on the left match potentially several rows on the right?).
  2. If you're aggregating something that "belongs to" the one side (a customer attribute, a film attribute), and the many side isn't unique per one-side row, you likely need DISTINCT inside the aggregate, or a restructured query.
  3. When in doubt, run the join without the aggregate first and manually inspect a few rows for a known entity — does a customer you expect to see once actually appear once, or five times?

Check yourself

  1. Why doesn't SELECT count(*) FROM customer JOIN rental ... error out even though it gives the wrong answer for "how many customers"?
  2. What's the fix when you need a distinct-entity count from a joined, fanned-out result?
  3. A report shows total revenue 3x higher than finance's own number, and you suspect a join is the cause. What's the first diagnostic question to ask about the query's joins?
← Previous Next →