UNION vs UNION ALL in SQL
💡 Concept Name
UNION vs UNION ALL – SQL set operations that combine result sets from multiple SELECT queries. UNION removes duplicates; UNION ALL retains them.
📘 Quick Intro
Both UNION and UNION ALL are used to combine the results of two or more SELECT statements. The key difference lies in handling duplicates: UNION removes them, while UNION ALL does not, making it faster but potentially verbose.
🧠 Analogy / Short Story
Imagine combining two guest lists. UNION is like merging the lists and removing duplicate names, ensuring no one gets invited twice. UNION ALL just stacks the lists as-is, even if some people appear multiple times. One is cleaner, the other is quicker.
🔧 Technical Explanation
- UNION combines results from two queries and removes duplicate rows automatically.
- UNION ALL also combines results but includes all duplicates from both queries.
- Both require that the SELECT statements have the same number of columns and compatible data types.
- UNION is slower due to the additional step of removing duplicates (sort + distinct).
- Use UNION when uniqueness is required; otherwise, prefer UNION ALL for performance.
💻 Real Code Example
-- Using UNION
SELECT name FROM employees
UNION
SELECT name FROM contractors;
-- Using UNION ALL
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;
❓ Interview Q&A
Q1: What is UNION in SQL?
A: It merges results of two queries and removes duplicates.
Q2: What does UNION ALL do?
A: It merges results without removing duplicates, making it faster.
Q3: Which is faster: UNION or UNION ALL?
A: UNION ALL, because it skips the duplicate elimination step.
Q4: When should you prefer UNION over UNION ALL?
A: When you want only distinct rows in the final result.
Q5: Do UNION and UNION ALL require the same number of columns?
A: Yes, and the corresponding columns must be of compatible data types.
Q6: Can you sort the final result after UNION?
A: Yes, by adding an ORDER BY after the final SELECT.
Q7: What happens if data types don’t match in UNION?
A: SQL will throw an error due to type incompatibility.
Q8: Can NULLs be duplicated in UNION ALL?
A: Yes, all rows including NULLs are retained as-is.
Q9: Is UNION ALL safe for audit logs or logs export?
A: Yes, since it maintains all data without filtering.
Q10: Does UNION guarantee sorting?
A: No, unless explicitly ordered using ORDER BY.
📝 MCQs
Q1. What does UNION do?
- Removes NULLs
- Combines result sets and removes duplicates
- Sorts only
- Joins tables
Q2. Which is faster: UNION or UNION ALL?
- UNION
- UNION ALL
- Both same
- Depends on DBMS
Q3. Which retains duplicate rows?
- UNION
- UNIQUE
- UNION ALL
- DISTINCT UNION
Q4. What condition must be met for UNION?
- Same table name
- Same row count
- Same number and type of columns
- One common column
Q5. Does UNION guarantee sorted output?
- Yes
- No
- Only in MySQL
- Only if indexed
Q6. What clause do you use to eliminate duplicates?
- JOIN
- INTERSECT
- UNION
- UNION ALL
Q7. Which operator should you use when duplicates are acceptable?
- UNION
- UNION ALL
- JOIN
- MERGE
Q8. Can UNION handle NULL values?
- No
- Yes
- Only in PostgreSQL
- Not with ORDER BY
Q9. Why might UNION be slower than UNION ALL?
- Because it sorts columns
- Because it removes NULLs
- Because it removes duplicates
- Because it creates temporary views
Q10. Can UNION be used between queries on different tables?
- No
- Yes, if columns match
- Only if keys match
- Only in views
💡 Bonus Insight
Use UNION ALL for better performance unless duplicates must be removed. Always use ORDER BY outside the final SELECT block if sorted output is needed.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!