Joins II — fan-out & grain

One-to-many multiplication before aggregation; detecting fan-out; many-to-many; grain as a declared property.

• ★Fan-out before aggregate

  Joining one-to-many multiplies the one side's rows; aggregating after the join counts the money once per match — aggregate first, dedupe, or use EXISTS.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE orders(id INT, amount INT); INSERT INTO orders VALUES (1,100),(2,50); CREATE TABLE shipments(order_id INT); INSERT INTO shipments VALUES (1),(1),(2);

  The trap

  SELECT SUM(o.amount) AS total FROM orders o JOIN shipments s ON s.order_id=o.id;

  250

  The fix

  SELECT SUM(o.amount) AS total FROM orders o WHERE EXISTS (SELECT 1 FROM shipments s WHERE s.order_id=o.id);

  150

  $250 is a believable revenue number — the timebomb that passes review, ships, and misstates revenue by the fan-out ratio.

• Detecting fan-out

  Compare COUNT(*) to COUNT(DISTINCT pk), or row counts before/after the join — a jump means multiplication.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE grow(batch VARCHAR, kilos INT); INSERT INTO grow VALUES ('B1',10),('B2',20); CREATE TABLE harvest(batch VARCHAR, tray VARCHAR); INSERT INTO harvest VALUES ('B1','t1'),('B1','t2'),('B2','t9');

  The trap

  SELECT SUM(g.kilos) AS kilos FROM grow g JOIN harvest h ON h.batch = g.batch;

  40

  The fix

  SELECT SUM(kilos) AS kilos FROM grow;

  30

  The joined SUM read 40 kilos, not 30 — batch B1 had two harvest rows, so the join multiplied it; COUNT(*) was 3 where COUNT(DISTINCT batch) was 2, and that gap is the inflation.

• Many-to-many through a bridge

  A bridge table multiplies both sides — the grain explodes in two directions at once.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE karts(kart VARCHAR, fee INT); INSERT INTO karts VALUES ('k1',10),('k2',10); CREATE TABLE bookings(kart VARCHAR, grp VARCHAR); INSERT INTO bookings VALUES ('k1','g1'),('k1','g2'),('k2','g3');

  The trap

  SELECT SUM(k.fee) AS value FROM karts k JOIN bookings b ON b.kart = k.kart;

  30

  The fix

  SELECT SUM(k.fee) AS value FROM karts k WHERE EXISTS (SELECT 1 FROM bookings b WHERE b.kart = k.kart);

  20

  The fleet value read 30 for karts worth 20 — joining through the bookings bridge made the grain kart×group, so each kart's fee counted once per booking.

• Accidental cross join

  A missing ON (or comma join) produces a Cartesian product — every row times every row.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE lanes(lane VARCHAR); INSERT INTO lanes VALUES ('L1'),('L2'),('L3'); CREATE TABLE throwers(thrower VARCHAR, lane VARCHAR); INSERT INTO throwers VALUES ('Ada','L1'),('Ben','L3');

  The trap

  SELECT l.lane, t.thrower FROM lanes l, throwers t ORDER BY l.lane, t.thrower;

  L1	Ada
  L1	Ben
  L2	Ada
  L2	Ben
  L3	Ada
  L3	Ben

  The fix

  SELECT l.lane, t.thrower FROM lanes l JOIN throwers t ON t.lane = l.lane ORDER BY l.lane;

  L1	Ada
  L3	Ben

  The league sheet paired every thrower with every lane — a comma join with no ON produced all 6 combinations, not the 2 real assignments.

• Grain as a declared property

  State 'one row per X' before writing — the grain is a decision, not an accident (bridge to data modeling).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE slips(slip VARCHAR, amount INT); INSERT INTO slips VALUES ('s1',5),('s2',3),('s1',5);

  The trap

  SELECT COUNT(*) AS slips FROM slips;

  3

  The fix

  SELECT COUNT(DISTINCT slip) AS slips FROM slips;

  2

  The depot counted 3 deposit slips where the grain is one-row-per-slip and only 2 slips exist — a double-scanned slip broke the assumed grain; the GROUP BY slip HAVING COUNT(*) > 1 probe catches the violator before it multiplies anything.