Difference Between UNION and UNION ALL in SQL
UNION merges two results and removes duplicates (slower); UNION ALL keeps everything including duplicates (faster). Output proof plus the 3 rules of UNION.
The two-registers analogy
The morning shift and evening shift both keep visitor registers, and some visitors came in both shifts. UNION = merging both lists and striking off repeated names — a clean list of unique visitors. UNION ALL = simply stapling one list under the other — everything kept, repeats included, done in a second. That extra work of finding and removing repeats is exactly why UNION is slower.
Output proof
morning_batch evening_batch
+--------+ +--------+
| name | | name |
| Aman | | Priya |
| Priya | | Kabir |
+--------+ +--------+
SELECT name FROM morning_batch
UNION
SELECT name FROM evening_batch;Aman
Priya
Kabir <- 3 rows: Priya appears ONCE (duplicate removed)
SELECT name FROM morning_batch
UNION ALL
SELECT name FROM evening_batch;Aman
Priya
Priya <- 4 rows: Priya appears TWICE (nothing removed)
Kabir
The 3 rules every UNION must follow
-- RULE 1: same NUMBER of columns in both SELECTs
SELECT name, class FROM students
UNION
SELECT name FROM teachers; -- ERROR: different column counts
-- RULE 2: matching column positions should have compatible TYPES
-- (name with name, number with number)
-- RULE 3: ORDER BY goes only ONCE, at the very end
SELECT name FROM morning_batch
UNION
SELECT name FROM evening_batch
ORDER BY name; -- sorts the COMBINED resultComparison table
| Point | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Kept |
| Speed | Slower (must sort/compare to dedupe) | Faster (just appends) |
| Row count | ≤ sum of both | Exactly sum of both |
| Use when | You need a unique list | Duplicates impossible or wanted |
Performance tip interviewers love: if you already KNOW the two results cannot overlap (e.g., students of 2024 and students of 2025 by admission year), use UNION ALL — you get the same result and skip the useless deduplication work. Choosing UNION "by default" on big tables is a common silent performance bug.
Where you actually use this
- Combining current and archived records:
SELECT * FROM orders UNION ALL SELECT * FROM orders_archive. - The FULL OUTER JOIN workaround in MySQL: LEFT JOIN UNION RIGHT JOIN (UNION here also removes the doubled matching rows — a neat two-birds trick).
- Building one dropdown list from two sources with no repeats → UNION.
- Interview line: "UNION merges and dedupes; UNION ALL merges and keeps everything — prefer UNION ALL whenever duplicates are impossible, for speed."
दो registers वाली analogy
Morning shift और evening shift दोनों के visitor registers हैं, और कुछ visitors दोनों shifts में आए. UNION = दोनों lists मिलाकर repeated names काट देना — unique visitors की साफ list. UNION ALL = एक list के नीचे दूसरी staple कर देना — सब रखा, repeats समेत, second में काम खत्म. Repeats ढूंढने-हटाने का वही extra काम UNION के धीमे होने की वजह है.
Output proof
morning_batch evening_batch
+--------+ +--------+
| name | | name |
| Aman | | Priya |
| Priya | | Kabir |
+--------+ +--------+
SELECT name FROM morning_batch
UNION
SELECT name FROM evening_batch;Aman
Priya
Kabir <- 3 rows: Priya EK बार (duplicate हटा)
SELECT name FROM morning_batch
UNION ALL
SELECT name FROM evening_batch;Aman
Priya
Priya <- 4 rows: Priya DO बार (कुछ नहीं हटा)
Kabir
हर UNION के 3 rules
-- RULE 1: दोनों SELECTs में columns की SAME संख्या
SELECT name, class FROM students
UNION
SELECT name FROM teachers; -- ERROR: column counts अलग
-- RULE 2: matching positions के columns के TYPES compatible हों
-- (name के साथ name, number के साथ number)
-- RULE 3: ORDER BY सिर्फ EK बार, बिल्कुल आखिर में
SELECT name FROM morning_batch
UNION
SELECT name FROM evening_batch
ORDER BY name; -- COMBINED result sort होता हैComparison table
| Point | UNION | UNION ALL |
|---|---|---|
| Duplicates | हटते हैं | रहते हैं |
| Speed | धीमा (dedupe के लिए sort/compare) | तेज़ (बस जोड़ता है) |
| Row count | ≤ दोनों का sum | Exactly दोनों का sum |
| कब use करें | Unique list चाहिए | Duplicates impossible हों या चाहिए हों |
Performance tip जो interviewers को पसंद है: अगर पहले से PATA है कि दोनों results overlap नहीं कर सकते (जैसे admission year से 2024 के students और 2025 के students), तो UNION ALL लगाइए — result वही मिलेगा और बेकार का deduplication बचेगा. बड़ी tables पर "by default" UNION लगाना common silent performance bug है.
असल में कहां use होता है
- Current और archived records मिलाना:
SELECT * FROM orders UNION ALL SELECT * FROM orders_archive. - MySQL में FULL OUTER JOIN का workaround: LEFT JOIN UNION RIGHT JOIN (यहां UNION doubled matching rows भी हटा देता है — एक तीर दो निशाने).
- दो sources से बिना repeats एक dropdown list → UNION.
- Interview line: "UNION मिलाकर dedupe करता है; UNION ALL मिलाकर सब रखता है — जहां duplicates impossible हों वहां speed के लिए UNION ALL prefer करें."
Frequently Asked Questions
What is the difference between UNION and UNION ALL?
UNION combines two result sets and removes duplicate rows, while UNION ALL combines them keeping every row including duplicates, which makes it faster.
Why is UNION ALL faster than UNION?
UNION must sort or hash the combined rows to detect and remove duplicates; UNION ALL simply appends the second result to the first with no comparison work.
What are the rules for using UNION?
Both SELECTs must have the same number of columns with compatible types at matching positions, and ORDER BY may appear only once at the very end for the combined result.