Q10: What is the difference between UNION and UNION ALL?
Both operators merge result sets from multiple SELECT queries into a single output set. They require matching column counts and compatible data types across positional indices.
- UNION: Combines result sets and performs an implicit duplicate elimination pass (sorting or hashing output records).
- UNION ALL: Concatenates result sets directly without deduplication, preserving all records.
High-Performance Set Merging
-- UNION ALL avoids expensive sorting overhead
SELECT customer_id, city FROM domestic_customers
UNION ALL
SELECT customer_id, city FROM international_customers;💡 Use UNION ALL when datasets are known to be mutually exclusive or when keeping duplicate rows is acceptable, avoiding costly distinct sorting operations.
Key Takeaways
- UNION performs duplicate removal; UNION ALL retains all rows directly.
- UNION ALL offers superior performance because it avoids expensive memory sorting or hash deduplication operations.