SQL UNION vs UNION ALL: Key Differences
💡 Concept Name
UNION and UNION ALL are SQL set operators used to combine the results of two or more SELECT queries.
📘 Quick Intro
UNION removes duplicate rows from the result set, while UNION ALL includes all rows, including duplicates. Both require the queries to have the same number and order of columns with compatible data types.
🧠 Analogy / Short Story
Imagine two guest lists for a party. If you merge the lists and remove duplicate names so each guest is invited only once, that’s like UNION. But if you merge the lists and keep duplicates because someone might be on both lists intentionally, that’s like UNION ALL. One prioritizes uniqueness, the other performance and completeness.
🔧 Technical Explanation
- 🔄
UNIONeliminates duplicate rows, which adds overhead due to sorting and comparison. - ⚡
UNION ALLreturns all rows as-is, including duplicates, resulting in better performance. - 📋 All SELECT statements in a UNION must have the same number of columns and compatible types.
- 📊 Use UNION when duplicates are irrelevant or misleading; use UNION ALL when performance is key and duplicates are meaningful.
- 🧠 Results can be further filtered, ordered, or grouped after the UNION/UNION ALL operation.
🎯 Purpose & Use Case
- ✅ Combine data from multiple tables or sources.
- ✅ Merge logs or audit entries from separate sources.
- ✅ Create unified reports from similar queries.
- ✅ Optimize performance by avoiding unnecessary duplicate elimination when using UNION ALL.
💻 Real Code Example
-- Table: Customers_India
-- Table: Customers_USA
-- Using UNION (removes duplicates)
SELECT Name FROM Customers_India
UNION
SELECT Name FROM Customers_USA;
-- Using UNION ALL (keeps duplicates)
SELECT Name FROM Customers_India
UNION ALL
SELECT Name FROM Customers_USA;
❓ Interview Q&A
Q1: What is the difference between UNION and UNION ALL?
A: UNION removes duplicates, UNION ALL keeps all records.
Q2: Which operator is faster in SQL: UNION or UNION ALL?
A: UNION ALL is faster because it doesn’t check for duplicates.
Q3: When would you prefer UNION over UNION ALL?
A: When you want only unique records in the combined result set.
Q4: Can UNION and UNION ALL combine different column types?
A: No, both require compatible column data types in order.
Q5: What happens if the number of columns doesn’t match in UNION?
A: SQL throws an error because UNION requires equal columns.
Q6: Does UNION preserve row order?
A: No, order isn’t guaranteed unless ORDER BY is used explicitly.
Q7: Can UNION ALL result in duplicate rows?
A: Yes, it returns all rows from both queries, duplicates included.
Q8: What is the default behavior for duplicates in UNION?
A: UNION automatically removes them.
Q9: Is performance a concern when using UNION?
A: Yes, because it requires sorting to eliminate duplicates.
Q10: Can you use ORDER BY with UNION ALL?
A: Yes, but it should be applied at the end of the final SELECT.
📝 MCQs
Q1. What does SQL UNION do?
- Deletes duplicates
- Combines and removes duplicates
- Keeps duplicates
- Combines tables only
Q2. Which operator is faster?
- UNION
- UNION ALL
- JOIN
- GROUP BY
Q3. What does UNION ALL include?
- Only unique rows
- Sorted values
- All rows, including duplicates
- Table schema
Q4. What must be the same in UNION?
- Table names
- Indexes
- Number and type of columns
- Primary key
Q5. Which one performs duplicate elimination?
- JOIN
- UNION
- UNION ALL
- GROUP BY
Q6. Does UNION ALL remove duplicates?
- Yes
- No
- Sometimes
- Only with WHERE clause
Q7. Which clause is used to sort UNION results?
- GROUP BY
- HAVING
- ORDER BY
- SORT
Q8. Why use UNION?
- To update rows
- To merge result sets and eliminate duplicates
- To delete records
- To backup tables
Q9. What happens if column count mismatches?
- Duplicate rows
- NULLs are added
- SQL error
- Column rename
Q10. Can you use UNION in subqueries?
- No
- Yes
- Only in MySQL
- Only with joins
💡 Bonus Insight
Use UNION when data deduplication is important (e.g., unique user emails). Use UNION ALL when dealing with raw logs, events, or non-critical duplicates for faster query times. Always apply ORDER BY after the final SELECT for consistent sorting.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!