← SQL roadmap

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.

IntermediateJunior · 1–3 yearsJoins

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?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.