UNION Removes Duplicates and UNION ALL Does Not, and That Difference Costs a Sort
UNION stacks the results of two queries and then removes duplicate rows; UNION ALL stacks them and stops there. Because removing duplicates means comparing every row with every other, UNION makes the engine sort or hash the entire combined result, while UNION ALL just streams it. When you already know the two halves cannot overlap, writing UNION ALL is a free speed-up, and writing UNION is paying for a guarantee you did not need.
Questions this Concept answers
- Why does `UNION` normally cost more than `UNION ALL` even when the two branches can never produce the same row?
Common mistakes: UNION vs. UNION ALL
UNION and UNION ALL both stack the results of two queries, but UNION also throws away duplicate rows. To find those duplicates the database has to sort or hash the whole combined result, which you ca…