Topic 43 of 52 · Intermediate
Why can joining two valid tables double the reported revenue?
A join emits every matching pair. Unique business entities in one table do not imply a unique join key in both tables; two child rows multiply one parent amount twice.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Detailed answer
A join emits every matching pair. Unique business entities in one table do not imply a unique join key in both tables; two child rows multiply one parent amount twice.
Declare the intended result grain first. Inspect key frequencies with GROUP BY key HAVING COUNT(*) > 1 on both sides and compare input/output row counts.
Pre-aggregate each many-side to the desired grain, or use EXISTS when only membership matters. DISTINCT on the final total hides symptoms and can delete legitimate equal values.
Internal working
For a key with m left rows and n right rows an equijoin yields m*n pairs. An optimizer chooses a join algorithm but cannot correct an incorrectly specified grain.
Practical scenario
Orders joined to both items and payments yield items×payments rows for each order. Summing order revenue after this join inflates totals.
SELECT order_id, COUNT(*) FROM payments GROUP BY order_id HAVING COUNT(*) > 1;
-- Aggregate payments before joining them to order-level metrics.Common mistakes
- Using DISTINCT as a universal repair.
- Joining two facts without defining grain.
Key takeaways
- Write the result grain first.
- Count matches on both sides.
- Aggregate before combining facts.
Follow-up challenges
- When does EXISTS avoid fan-out?
- How do you validate a supposed one-to-one relationship?