UNION vs UNION ALL — why does the wrong one quietly slow your query?
⚡ Short Answer
UNION removes duplicates (an implicit DISTINCT → sort/hash, extra cost). UNION ALL concatenates results and keeps duplicates (no dedup work). Use UNION ALL whenever you know results are already disjoint — it's significantly faster.
☕Coffee Chat Question
Concept Made Simple
“UNION vs UNION ALL — why does the wrong one quietly slow your query?”
🧠Mind Map Answer
Remember It Faster
🔥What If?
Think Beyond the Expected
A report combining 6 monthly tables is slow — quick fix?
If the monthly partitions can't overlap, switch UNION to UNION ALL. UNION forces a costly dedup across the whole combined set; UNION ALL just appends, often turning a sort-heavy plan into a cheap concatenation.
😂Real World
Defaulting to UNION 'to be safe' is a frequent hidden cost in reports that combine partitioned/disjoint sources where duplicates are impossible.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗Using UNION when duplicates are impossible
- ✗Assuming UNION ALL sorts the result
- ✗Mismatched column counts/types across parts
✅Best Practices
- ✓Prefer UNION ALL unless dedup is needed
- ✓Ensure disjoint sources before using UNION ALL
- ✓Check the plan for an unexpected sort/dedup
🔁Follow-up Questions
- 1How does the DB implement the UNION dedup?
- 2When is UNION (dedup) actually required?
- 3What rules govern column matching across the parts?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "UNION vs UNION ALL — why does the wrong one quietly slow your query?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.