UNION combines the rows of two queries and removes duplicate rows, while UNION ALL combines them and keeps every row, duplicates included.
Removing duplicates costs work, because the database has to sort or hash the combined rows to find repeats. So UNION ALL is usually faster. UNION also removes repeats that sit inside one input, not only the ones shared by both.
Both need the same number of columns in each query, in the same order, with compatible data types. The result takes its column names from the first query.
Use UNION ALL when the rows are already distinct, or when duplicates carry meaning, such as when counting events. Use UNION when each distinct row should appear once.
SELECT name FROM employees_2023
UNION -- Removes duplicates
SELECT name FROM employees_2024;
SELECT name FROM employees_2023
UNION ALL -- Keeps all rows
SELECT name FROM employees_2024;
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
The diagram below the answer is the concept . Jump to it ↓