What is the difference between UNION and UNION ALL?

Both UNION and UNION ALL stack the results of two SELECT statements on top of each other. The difference is what happens to duplicates.

UNION: removes duplicates

SELECT name FROM employees
UNION
SELECT name FROM contractors

If "Alice" appears in both tables, the result shows her once. UNION does a DISTINCT on the combined output.

The cost: deduplication requires sorting or hashing the entire result set. On large datasets, this is slow.

UNION ALL: keeps everything

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors

If "Alice" appears in both tables, the result shows her twice. No deduplication. No extra work.

The cost: you might get duplicates when you don't want them.

When to use which

Use UNION when you genuinely need unique rows. Merging lists where overlap is possible and duplicates would be misleading.

Use UNION ALL when you know there are no duplicates, or when duplicates are acceptable. This is the faster option and should be your default unless you have a reason to deduplicate.

In practice, I use UNION ALL about 90% of the time. If I need deduplication, I usually want more control over it than UNION provides -- like deduplicating on specific columns rather than the entire row.

Rules both share

• Both require the same number of columns in each SELECT
• Column types must be compatible
• Column names come from the first SELECT
• You can chain multiple UNION or UNION ALL statements

SELECT name, 'employee' AS source FROM employees
UNION ALL
SELECT name, 'contractor' AS source FROM contractors
UNION ALL
SELECT name, 'intern' AS source FROM interns

Adding a source column is a useful pattern. It tells you where each row came from after the merge.