performance of union versus union all

Question

I have to run a select statement across several tables. I am sure the tables return different records. I am anyway using UNION ALL.

Is it better to use UNION or of UNION ALL in performance terms when I am sure the tables return different records?

Answer 1

UNION ALL always is faster, because UNION exclude duplicated entries

Answer 2

It is better to use UNION ALL when you know you want all the result rows, whether or not you know they'll be distinct or not. UNION without "all" will always perform the "distinct check", regardless of what the data actually is.

Answer 3

Why is UNION ALL faster? Because UNION must do a sort to remove the duplicates. If you do not need to remove duplicates then UNION ALL is the better option, however UNION does have a purpose and should be used when appropriate.

Answer 4

UNION implement internally two queries. 1.SELECT which will return a dataset 2.DISTINCT. Anyone who has studied database internals can easily understand that a DISTINCT clause is extremely costly in terms of processing.

If you are pretty sure that the resultant dataset need not have unique rows then we can skip UNION and use UNION ALL instead.

UNION ALL will be same as UNION except that it doesn't fire a DISTINCT internally sparing us costly operations

Answer 5

UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation. See: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison