What is the difference between UNION and UNION ALL in SQL?
Both `UNION` and `UNION ALL` combine the result sets of two or more `SELECT` queries into a single result set with the same column count and compatible types.
• `UNION` removes duplicate rows from the final result set by performing an implicit sorting/hash distinct operation, which can be computationally expensive. • `UNION ALL` preserves all rows, including duplicates, without sorting. It is substantially faster and should always be preferred when results are known to be mutually exclusive or when duplicates are desirable.
-- Combining archived orders and active orders efficiently
SELECT order_id, customer_id, total, 'Active' AS status
FROM ActiveOrders
UNION ALL
SELECT order_id, customer_id, total, 'Archived' AS status
FROM ArchivedOrders;Because an order is either active or archived (never both), duplicates are impossible, making `UNION ALL` much more efficient by avoiding a pointless in-memory deduplication sort.
Ready for Query Execution Plans?
Take a quick test to evaluate yourself, or move straight to the next module.