◐ ai-teacher

🗄 SQL & Databases · Chapter 5

Joins in depth — and the fan-out trap

Everyone can name the join types. The question that separates candidates is: how many rows come out?

Study this lesson free in the app →

The basics

A join combines rows from two tables by matching values in a column they share — each ride matched to its driver by driver_id, for example.

The problem it solves: good databases store each fact once — rides, drivers and payments in separate tables. But every real question ("revenue per driver city", "rides with no payment") needs columns from several tables at once. A join stitches them back together at query time.

The mental model: for each row on the left, go and find every row on the right that matches. If you find exactly one, the row passes through unchanged. If you find none, an inner join drops the row and a left join keeps it with NULLs. If you find three, the left row is copied three times — and every sum after that counts it three times. Nearly every wrong number in analytics comes from forgetting that last case.

Words you must know 5

INNER / LEFT / FULL OUTER
keep matches only / all left rows / everything from both sides.
Join key
the column(s) in the ON clause.
Cardinality
for one left row, how many right rows can match (one-to-one, many-to-one, one-to-many).
Fan-out
a join that multiplies rows because the right side isn't unique on the key.
Anti join
"rows with no match", via NOT EXISTS or LEFT JOIN … WHERE right.key IS NULL.

Explained

SELECT sum(r.fare)
FROM rides r
JOIN drivers d ON r.driver_id = d.driver_id;

This looks like it sums the fares. It sums the fares once per matching driver row — so if drivers has one row per driver per vehicle, and most drivers have two vehicles, your revenue is roughly doubled.

No error. No warning. A believable number that's twice the truth.

This single failure — the fan-out join — is the most common cause of wrong figures in analytics, and it's entirely preventable by asking one question before every join.

The wedding seating list

You have a list of guests and a list of dietary requirements.

One guest, one requirement each → merge them and you still have the same number of guests. Fine.

But some guests listed three requirements: vegetarian, no nuts, no dairy. Merge now and that guest appears three times on your seating chart. Count the chart and you're ordering three chairs for one person.

Nothing about the merge was wrong. The relationship was one-to-many, and you assumed one-to-one.

That's every fan-out bug. The join did exactly what it was told; the mistake happened before it, when someone assumed a key was unique and never checked.

The four joins, and what each keeps

-- INNER: rows that match on both sides
FROM rides r JOIN drivers d ON r.driver_id = d.driver_id

-- LEFT: all rides, plus driver columns where matched (NULL where not)
FROM rides r LEFT JOIN drivers d ON r.driver_id = d.driver_id

-- FULL OUTER: everything from both sides, NULLs where unmatched
FROM rides r FULL OUTER JOIN drivers d ON ...

-- CROSS: every combination — deliberate only
FROM dates CROSS JOIN cities

LEFT JOIN is the default for data engineering, because it preserves your fact table. An INNER JOIN silently deletes rides whose driver is missing from the dimension — which is data loss disguised as a join.

Two more that avoid fan-out entirely, and are under-used:

-- SEMI join: does a match exist? Returns left rows only, never duplicates
WHERE EXISTS (SELECT 1 FROM active_drivers a WHERE a.driver_id = r.driver_id)

-- ANTI join: rows with no match
WHERE NOT EXISTS (SELECT 1 FROM active_drivers a WHERE a.driver_id = r.driver_id)

When you only need to test membership rather than pull columns, EXISTS is the correct tool — and it cannot multiply your rows.

Watch three rows become five

Count the rows before and after. That single check catches this every time.

Cardinality: the question to ask first

Before writing any join, answer: for one row on the left, how many rows can match on the right?

| Relationship | Row count | Safe? | | --- | --- | --- | | many-to-one | unchanged | ✅ the normal case | | one-to-one | unchanged | ✅ | | one-to-many | multiplies | ⚠️ only if you intend it | | many-to-many | explodes | 🚨 almost always a bug |

A fact-to-dimension join should be many-to-one: many rides, one driver. If the dimension isn't unique on the join key, it isn't a dimension yet.

Verify it in one query, before you trust anything:

-- is the right side actually unique on the key?
SELECT driver_id, count(*)
FROM drivers GROUP BY driver_id HAVING count(*) > 1;
-- any rows returned = fan-out risk

And check the join itself:

SELECT count(*) FROM rides;                    -- 4,000,000
SELECT count(*) FROM rides r JOIN drivers d ON ...;  -- must still be 4,000,000

In Pandas this check is built in — validate="many_to_one" raises instead of silently tripling. SQL has no equivalent, so you are the validation.

Same query, three engines, three row counts

SELECT r.ride_id, r.fare, p.amount
FROM rides r
LEFT JOIN payments p ON r.ride_id = p.ride_id
WHERE p.status = 'captured';

rides has 1,000 rows. Some rides have no payment row at all.

The author intended: all rides, with payment details where a captured payment exists.

How production fan-outs actually happen

Nobody writes a fan-out join deliberately. They appear when something upstream changes shape.

The dimension gains a row. drivers was one row per driver. Then vehicles were added, and it became one row per driver per vehicle. Every existing query that joined to it silently started double-counting. No deploy happened on the analytics side.

Slowly changing dimensions. A Type 2 dimension keeps history: one row per driver per version. Joining a fact to it without a validity-date filter multiplies every fact by the number of times that driver's record ever changed.

JOIN dim_driver d
  ON r.driver_id = d.driver_id
 AND r.ride_date >= d.valid_from
 AND r.ride_date <  d.valid_to      -- ← without this, fan-out

Chained joins. Each join is fine on its own; three in sequence multiply. rides → drivers(×2) → vehicles(×2) gives you 4× the rows, and the ratio is now non-obvious.

The defences that scale:

  1. Aggregate before joining. Collapse the many side to one row per key first, then join
  2. Test uniqueness in the pipeline, not in your head — dbt's unique test on every dimension key
  3. Assert the row count after a join in any production model

The first one is the most useful and least used: if you need each driver's total, GROUP BY driver_id in a CTE first, then join one row per driver to the facts.

Watch an INNER JOIN, row by row

Ride 103's driver d9 has no match, so it vanishes. And the duplicate d2 row doubles ride 102 — the classic fan-out that inflates a SUM.

Words to know

CardinalityHow many rows on one side can match one row on the other.
Fan-out joinA join on a non-unique key that multiplies rows and inflates later aggregates.
Semi joinEXISTS — tests for a match without adding columns or duplicating rows.
Anti joinNOT EXISTS — returns left rows with no match on the right.
Join keyThe column(s) matched in the ON clause.
SCD Type 2A dimension keeping history as one row per version, with validity dates.
Grain of a dimensionWhat one row represents — the thing that must be unique on the join key.
ON vs WHEREON filters while matching; WHERE filters the result — which cancels an outer join.

In two minutes

Test yourself

1. Rides joined to a drivers table with 2 rows per driver. What happens to sum(fare)?

  1. Unchanged
  2. Roughly doubled — each fare is counted once per matching driver row
  3. Halved
  4. An error is raised
Show the answer

Roughly doubled — each fare is counted once per matching driver row — The join multiplies rows before the aggregate runs. An exact multiple in a wrong total is the fan-out signature.

2. LEFT JOIN payments p ON ... WHERE p.status = 'captured' — what does this do?

  1. Keeps all left rows as intended
  2. Silently becomes an INNER JOIN, dropping unmatched rows
  3. Returns only NULL rows
  4. Raises an error
Show the answer

Silently becomes an INNER JOIN, dropping unmatched rows — Unmatched rows have NULL status; NULL = 'captured' is unknown, so WHERE discards them. Put the condition in ON.

3. You need to know whether a match exists but want no columns from the other table. Use…

  1. INNER JOIN
  2. EXISTS
  3. CROSS JOIN
  4. FULL OUTER JOIN
Show the answer

EXISTS — A semi join tests membership without duplicating rows — it removes fan-out risk entirely.

4. What is the one check that catches most join bugs?

  1. Reading the query twice
  2. count(*) before and after the join — for fact-to-dimension it must be unchanged
  3. Adding DISTINCT
  4. Using LEFT JOIN everywhere
Show the answer

count(*) before and after the join — for fact-to-dimension it must be unchanged — SQL has no equivalent of Pandas' validate='many_to_one', so you are the validation.

5. Why is INNER JOIN risky on a fact table?

  1. It is slower
  2. It silently deletes facts whose dimension row is missing — data loss disguised as a join
  3. It cannot use indexes
  4. It duplicates rows
Show the answer

It silently deletes facts whose dimension row is missing — data loss disguised as a join — LEFT JOIN preserves the facts and leaves NULLs, which is visible. Missing rows are not.

6. You need every ride that has no payment row. Which query is correct?

  1. rides r JOIN payments p ON r.ride_id = p.ride_id WHERE p.amount IS NULL
  2. rides r LEFT JOIN payments p ON r.ride_id <> p.ride_id
  3. rides r CROSS JOIN payments p WHERE r.ride_id <> p.ride_id
  4. rides r LEFT JOIN payments p ON r.ride_id = p.ride_id WHERE p.ride_id IS NULL
Show the answer

rides r LEFT JOIN payments p ON r.ride_id = p.ride_id WHERE p.ride_id IS NULL — The LEFT JOIN keeps unmatched rides with NULL payment columns; filtering on the right-side key being NULL keeps only those. An inner join can never return a ride without a payment, and <> matches nearly everything.

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.

Learn it so it stays. Read it, test yourself, then recall it on two different days. The app schedules that for you, free.

Start this lesson →