Difference between UNION and UNION ALL
Difference between UNION and UNION ALL
Both operators combine the result sets of two or more SELECT queries into a single result set.
Key Differences
| Property | UNION | UNION ALL |
|---|---|---|
| Duplicate Rows | Removed (distinct values only) | Retained (all rows included) |
| Performance | Slower (requires sorting / hashing) | Fast (direct append) |
| Memory Usage | Higher (sort buffer / hash table) | Minimal (streaming output) |
| Best Practice | Use only when duplicates must be removed | Default choice for performance |
Syntax Example
-- Returns unique names from both tables:
SELECT name FROM customers
UNION
SELECT name FROM suppliers;
-- Returns all names, including duplicates (Much faster):
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;