mediumCodingConceptEntryMidSenior
Adding a second LEFT JOIN doubled the revenue figure on a report. Explain what happened and write the correct query.
SQL join duplicates cause double counting when two one-to-many joins multiply rows before aggregation. Aggregate each child table to one row per parent first, then join those results back to the parent. Use this sql queries answer to show the decision, trade-off, and evidence rather than a memorised definition.
4 min readUpdated 2026-08-15
Answer
Short answer
When a second join doubles totals, pre-aggregate each one-to-many branch before joining. DISTINCT can hide symptoms, but the reliable fix is to restore one row per parent before the final SUM.
Was this answer helpful?
Likely follow-ups
- Rewrite it with lateral subqueries. When is that the better shape?
- How would you get goods, payments and refunds in one pass without three subqueries?
- The same report needs a count of orders with no payment at all. How?
- Why does NOT IN against a subquery containing a NULL return nothing?
Related questions
- Write a query returning the second-highest salary in each department, then tell me what it does when two people tie for the top.mediumAlso on sql-queries and null-handling3 min
- Find me every customer with no order in the last thirty days, then tell me which ways of writing that get NULLs wrong.mediumAlso on joins4 min
- Your Collectors.toMap call passed every test and threw in production. What did the code assume, and what are your options?mediumAlso on null-handling4 min
- You need a work queue with priorities and you are told to build it in Postgres, not Kafka. What does the dequeue statement have to do, and what happens when ten workers run it at once?mediumAlso on sql-queries6 min