UNION vs UNION ALL?

Options

1. They are identical in the result and in the speed of the query
2. UNION removes duplicates; UNION ALL keeps every row and is faster (Correct option)
3. UNION ALL removes duplicates; UNION keeps every row of both
4. UNION only works when both of the queries use the same table

Answer

UNION removes duplicates (extra sort/hash cost); UNION ALL keeps all rows and is faster

Why

Both stack the results of two queries on top of each other.

UNION then removes duplicate rows, which requires extra work (sorting or hashing).

UNION ALL keeps everything and is faster. Use it when duplicates are impossible or acceptable.

Remember

Prefer UNION ALL unless you need to remove duplicates.

Key terms

• UNION
• SQL
• Sorting algorithm
• Aggregate function