A report total inflates after you add a JOIN — sometimes exactly double, sometimes an odd amount. Row counts grow too: 4,000 orders go into a join and 4,700 rows come out. No error, no warning. The query runs green and the number is wrong in a meeting.
First weeks of writing joins — enriching a transactions table with a store, customer,
or route master. Most common when the "master" table was exported from Excel or built
by hand and quietly holds the same key twice, or when the join key is not as unique as
everyone assumed. The same failure appears in pandas merge (WR-014); this is its SQL
face. Linked module: 0.6 (SQL for AI people).
A join returns one row for every matching PAIR. If key S2 appears once on the left and
twice on the right, every S2 order matches both copies and appears twice — and every
sum over those rows counts S2's money twice. Confirm in two steps. (1) Compare counts:
SELECT COUNT(*) FROM orders versus the joined query's count — any growth on a
many-to-one join is proof of duplicate keys on the "one" side. (2) Find the culprits:
SELECT store_id, COUNT(*) FROM stores GROUP BY store_id HAVING COUNT(*) > 1. That
query lists every key that will multiply your rows. Exact doubling means one duplicate;
messier inflation means several keys with different repeat counts.
De-duplicate the lookup side before joining: fix the source data if you can, or join
against a cleaned version — SELECT DISTINCT store_id, name, city FROM stores when
whole rows repeat, or a GROUP BY picking one deliberate row per key when they differ.
Then re-run the joined count and confirm it matches the left table's count again.
Do not divide the total by two: that hides the disease, mis-corrects every key that
repeated a different number of times, and breaks again on the next refresh.
Print row counts before and after every join, every time — one line of discipline
catches this whole class of bugs. Run the HAVING COUNT(*) > 1 uniqueness check on any
table you plan to use as a lookup, especially anything that ever passed through Excel.
In pandas, the same contract is merge(..., validate="many_to_one"), which raises
instead of silently multiplying.
Q: "Revenue doubled after you added a join to the store master. Walk me through your
diagnosis." Strong answer: state the pair-matching rule in one sentence, compare pre-
and post-join row counts as the first piece of evidence, show the GROUP BY ... HAVING
COUNT(*) > 1 query to find duplicate keys, and close by rejecting divide-by-two as a
fix in favour of de-duplicating the lookup table. Mentioning the pandas
validate="many_to_one" parallel signals cross-tool understanding.