26

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?

UnDiUdin
  • 14,334
  • 37
  • 140
  • 233
  • possible duplicate of [To union or union all, that is the question](http://stackoverflow.com/questions/3909563/to-union-or-union-all-that-is-the-question) – Lukas Eder May 08 '12 at 12:52

5 Answers5

39

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

Joe Stefanelli
  • 128,689
  • 18
  • 228
  • 231
5

UNION ALL always is faster, because UNION exclude duplicated entries

Michael Pakhantsov
  • 24,077
  • 5
  • 58
  • 59
  • 2
    It's not "always" faster. I'm doing some performance tweaking right now and finding UNION almost twice as fast as UNION ALL even though the resulting query returns the exact same set of results. This is MS SQL Server. I can't explain it and was hoping someone else could. – Peter Moore Sep 06 '20 at 18:31
  • @PeterMoore Did you also have no duplicates like me? I also cannot see any speed difference between the two when checking it without any tools just on a 10s query, but there is also no reason not to believe that `union all` must be faster for large tables aso. Perhaps something that gets important only if there *are* cases that make `union` and `union all` return different results = if you have duplicates. Makes no sense, I know. – questionto42standswithUkraine Apr 22 '22 at 19:09
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

Vijjendra
  • 22,463
  • 11
  • 57
  • 90
3

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.

Philip Kelley
  • 38,242
  • 11
  • 58
  • 91
2

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.

Mike Ritacco
  • 12,094
  • 2
  • 15
  • 5