PrismitaLEARN · PRACTICE · GROW

What is the difference between UNION and UNION ALL?

SQL Server T-SQL Basic Fresher1–2 Yrs SQL Query 1 min read

Quick Answer

UNION combines two result sets and removes duplicate rows, which requires a sort or hash — extra work. UNION ALL just concatenates them, keeping duplicates, and is significantly faster. Use UNION ALL whenever you know the sets don't overlap or duplicates are acceptable.

UNION stacks two result sets and then removes duplicates, which means a sort or a hash aggregate under the hood. UNION ALL just concatenates them and keeps everything, so it's much cheaper. My default is UNION ALL — I only use plain UNION when duplicates are actually possible and I need them gone. People often use UNION on two sets that can't overlap anyway, and that's just paying for a dedup that does nothing.

  • Both require the same number of columns with compatible types.
  • UNION adds a distinct sort/aggregate operator to the plan; on large sets that's real CPU and tempdb usage.
  • UNION ALL is a simple concatenation — no dedup, no sort.
  • Common mistake: reaching for UNION out of habit when the inputs are already disjoint (e.g. active orders + archived orders) — you pay for a dedup that can never do anything.
All SQL Server questions More on T-SQL