Beginner LevelSQL & MySQL
What is the difference between UNION and UNION ALL?
❓ Interview Prompt: Compare `UNION` vs `UNION ALL`, duplicate removal overhead, memory sorting, and combining query result sets.
Both 'UNION' and 'UNION ALL' combine the output rows of two or more 'SELECT' queries into a single result set:
Key Differences:
•
'UNION': Combines result sets and removes duplicate rows. Requires an internal sorting or hashing step, adding performance overhead.
•
'UNION ALL': Combines result sets without removing duplicate rows. Fast and efficient since no deduplication sort is required.
•
Rule: Both queries must project the same number of columns with compatible data types!
Code Implementation Example
sql
-- UNION ALL preserves duplicates and executes faster
SELECT id, email, 'active' AS status FROM active_users
UNION ALL
SELECT id, email, 'archived' AS status FROM archived_users;💡Combining query result sets efficiently using UNION ALL.
🎯 Interview Key Takeaway
Use UNION ALL by default when datasets are known to be distinct or when keeping duplicate rows is acceptable.
#SQL#UNION#UNION ALL#Performance#Beginner
Back to all SQL & MySQL Questions→