EasyExperience level: 0-2 years · 3-5 years1 min read

UNION vs UNION ALL — why does the wrong one quietly slow your query?

Reviewed by Gurusankar M.

Asked inInfosysCognizantAccenture
#union#union all#distinct#performance
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“UNION vs UNION ALL — why does the wrong one quietly slow your query?”

Remember It Faster

Mind Map Answer

UNION
dedups → sort/hash, slower
UNION ALL
keeps all rows, no dedup, faster
Rule
UNION ALL unless you NEED dedup

Think Beyond the Expected

What If?

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:

  • UNION dedups (DISTINCT cost)
  • UNION ALL keeps duplicates
  • UNION ALL faster on disjoint sets
  • column count/type must match

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

  1. How does the DB implement the UNION dedup?
  2. When is UNION (dedup) actually required?
  3. What rules govern column matching across the parts?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.