Growth Notes

Joins

Tags: web-dev analytics concept

Joins

Date: 2026-09-27

A join pairs every row on the left with every matching row on the right. The kind of join only decides what happens to rows with no match — and the thing that actually breaks analytics numbers is rows with more than one match.

A join combines two tables into one result by pairing rows whose join condition is true — conceptually every left row against every right row, keeping the pairs that match.

Two tables

orders                              refunds
order_id  customer  total           order_id  amount
1         ana       40              1         10
2         ben       25              1          5     ← two refunds on order 1
3         cai       60              9         30     ← refund for an order not in orders

The four kinds, as row sets

INNER JOIN   only pairs that match

order_id  total  amount
1         40     10
1         40      5         ← order 1 now appears twice
                             orders 2, 3 gone; refund on 9 gone

LEFT JOIN    every left row; NULL where nothing matched

order_id  total  amount
1         40     10
1         40      5
2         25     NULL       ← kept, no refund
3         60     NULL

RIGHT JOIN   every right row — a LEFT JOIN with the tables swapped

order_id  total  amount
1         40     10
1         40      5
9         NULL   30         ← refund with no matching order

FULL OUTER JOIN   both sides kept

order_id  total  amount
1         40     10
1         40      5
2         25     NULL
3         60     NULL
9         NULL   30

Notice order 1 in every result. Choosing the join kind never protected it from duplication. SUM(total) over any of these gives 40 too much.

  • CROSS JOIN — every row against every row, no condition. 3 × 3 = 9 rows. Useful deliberately (every date × every product, to fill gaps with zeros); disastrous by accident, which is what a missing join condition produces
  • Self join — a table joined to itself under two aliases. Each session against the previous session for the same user, before window functions made that easier — Window Functions
  • RIGHT JOIN is rarely written. Swap the table order and use LEFT; everyone reads left joins faster

Fan-out: the bug that inflates revenue

Fan-out is a join multiplying rows because the join key isn’t unique on one side. It’s silent — no error, just a bigger number.

-- wrong: order 1's total is counted once per refund
SELECT SUM(o.total) AS revenue, SUM(r.amount) AS refunded
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.order_id;
-- revenue = 40 + 40 + 25 + 60 = 165   (true answer: 125)
 
-- right: collapse the many side to one row per key before joining
WITH refund_totals AS (
  SELECT order_id, SUM(amount) AS refunded
  FROM refunds
  GROUP BY order_id                       -- now unique on order_id
)
SELECT SUM(o.total) AS revenue, SUM(COALESCE(rt.refunded, 0)) AS refunded
FROM orders o
LEFT JOIN refund_totals rt ON rt.order_id = o.order_id;
-- revenue = 125, refunded = 15

The rule: aggregate the many side down to the grain of the one side, then join. Knowing the grain — what one row means — of every table is what lets you see this before running anything — Common Table Expressions · Double Counting.

The check: count rows before and after the join. If a LEFT JOIN returns more rows than the left table has, something on the right isn’t unique.

SELECT DISTINCT is the usual reflex fix and the wrong one. It hides the duplication without explaining it, and it collapses genuinely identical rows that should have counted twice.

Where LEFT JOINs quietly become INNER

-- intended: all orders, with refunds where they exist
SELECT o.order_id, r.amount
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.order_id
WHERE r.amount > 0;            -- removes every NULL row → now an inner join
 
-- fix: put the condition on the right table inside the ON
LEFT JOIN refunds r ON r.order_id = o.order_id AND r.amount > 0

WHERE runs after the join, and NULL > 0 isn’t true, so every unmatched row is discarded — Aggregation and Grouping has the evaluation order. A filter on the right table of a LEFT JOIN belongs in ON; a filter on the left table belongs in WHERE.

Other things that bite

  • NULL never matches NULL. A join on a nullable column drops the nulls on both sides — The Relational Model
  • Type mismatch on the key. '123' joined to 123 works by implicit cast in some databases, fails in others, and in some silently prevents index use — SQL Dialects
  • Anti-joins — “orders with no refund” — are LEFT JOIN ... WHERE r.order_id IS NULL, or NOT EXISTS. Avoid NOT IN against a column that can contain NULL: one null makes the whole predicate unknown and it returns nothing
  • Semi-joins — “customers who have ordered” — are EXISTS, which can’t fan out, rather than a join plus DISTINCT

Joins in application code rather than SQL are the N+1 Queries problem; slow joins are usually a missing index on the join key — Indexing · Query Performance.

Graph View

Backlinks