UNION and UNION ALL are both SQL operations used to combine the result sets of two or more SELECT queries, but they differ in how they handle duplicate records:
1. Duplicates Handling:
- UNION: Combines the result sets of two or more SELECT statements and eliminates duplicate rows from the final result. This means that if there are any duplicate records in the combined results, only one instance of each duplicate will be included.
- UNION ALL: Combines the result sets of the SELECT statements but does not remove duplicates. All records, including duplicates, are included in the final result set.
2. Performance:
- UNION: May be slower than UNION ALL because it requires additional processing to check for and eliminate duplicates.
- UNION ALL: Generally faster than UNION because it does not perform the duplicate elimination step.
3. Use Cases:
- Use UNION when you need a distinct set of results and are concerned about duplicate entries in the combined datasets.
- Use UNION ALL when you want to include all records from the datasets (including duplicates) and prioritize performance.
Example
Assuming we have two tables, TableA and TableB:
- TableA:
id
----
1
2
3
- TableB:
id
----
2
3
4
Using UNION and UNION ALL:
-- Using UNION
SELECT id FROM TableA
UNION
SELECT id FROM TableB;
-- Result:
-- id
-- ----
-- 1
-- 2
-- 3
-- 4
-- Using UNION ALL
SELECT id FROM TableA
UNION ALL
SELECT id FROM TableB;
-- Result:
-- id
-- ----
-- 1
-- 2
-- 3
-- 2
-- 3
-- 4
In this example:
- The UNION operation returns distinct IDs (1, 2, 3, 4), while UNION ALL returns all IDs, including duplicates (1, 2, 3, 2, 3, 4).