✏️ Explanatory Question

What is the difference between UNION and UNION ALL?

👁 8 Views
📘 Detailed Answer
🟢 Easy
← Previous What is the difference between UNION and JOIN?
💡

Answer with Explanation

41

What is the difference between UNION and UNION ALL?

Level: Intermediate — A very common question that also tests your awareness of performance.

Both UNION and UNION ALL combine the results of two or more SELECT statements vertically (stacking rows). The difference is how they handle duplicate rows.

  • UNION: Removes duplicate rows from the combined result — it performs an extra sort/dedupe step.
  • UNION ALL: Keeps all rows including duplicates — no dedupe step, so it's faster.
Performance key point: UNION ALL is faster because it skips the duplicate-elimination step. If you know there are no duplicates (or you want to keep them), always prefer UNION ALL.

Side-by-Side Comparison

Feature UNION UNION ALL
Duplicates Removed Kept
Extra dedupe step Yes (sort/compare) No
Performance Slower Faster
Result size Smaller / equal Larger / equal
Use when You need unique rows Duplicates are fine/wanted

Illustrative Scenario

Suppose two tables both contain the city 'Kolkata':

UNION

  • 'Kolkata' appears once
  • Duplicates eliminated

UNION ALL

  • 'Kolkata' appears twice
  • All rows preserved

Quick Example

-- UNION: unique cities only
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL: all cities, duplicates preserved (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- Common use: combining monthly data where duplicates matter
SELECT amount FROM jan_sales
UNION ALL
SELECT amount FROM feb_sales;   -- keep every transaction
Interviewer tip: The one-liner they want — "UNION removes duplicate rows (with an extra dedupe step, so slower), while UNION ALL keeps all rows including duplicates and is faster. Use UNION ALL unless you specifically need unique rows."
← Previous What is the difference between UNION and JOIN?