What is the difference between UNION and UNION ALL?
Both combine the result sets of two or more queries vertically (column count and types must match).
UNION — combines and removes duplicates. Sorts internally to deduplicate, so it's slower.
UNION ALL — combines and keeps duplicates. Faster because no deduplication is performed.
Use UNION ALL by default; only use UNION when you specifically need distinct rows.
-- UNION: removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- Returns unique cities from both tables
-- UNION ALL: keeps duplicates (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
-- Returns ALL cities, including duplicates
-- Practical: combine active and archived orders
SELECT id, total, 'active' AS source FROM orders
UNION ALL
SELECT id, total, 'archived' AS source FROM archived_orders
ORDER BY total DESC;UNION removes duplicate cities — if 'NYC' is in both tables, it appears once. UNION ALL keeps all rows — 'NYC' appears multiple times.
The order example combines active and archived orders with a source label. ORDER BY applies to the final combined result.
UNION = removes duplicates (slower). UNION ALL = keeps all (faster). Always prefer UNION ALL when duplicates are acceptable (performance).
Both require matching column count and types. ORDER BY applies to the final result.