If you’re like me, after using UNION for many years you may have stumbled upon the UNION ALL and thought, what the heck is the difference? Well it’s pretty simple. UNION performs a distinct and gives you the unique results for all UNION’ed queries, whereas UNION ALL does not perform a distinct and gives you all rows regardless of whether they are duplicate. The performance difference is that UNION ALL can be much faster since distinct is an expensive operation.
Here is a simple example detailing the difference. We’ll populate a single temp table with one record, then we’ll do both a UNION, and a UNION ALL to see the results.
name = 'derek'
,detail = 'this is record 1'
-- perform the UNION on the same table
-- perform the UNION ALL on the same table
From the results we see below, the UNION only returns one record.
The execution plans are identical except for the distinct operation being performed as the last operation before output. From this example we see how much utilization distinct is consuming relational to the other operations. (It’s a lot, however this is a rather simple operation)
Long story short, if you know you’re two separate result sets contain distinct values, it’s best to use UNION ALL. If for some reason you can’t guarantee the uniqueness, only then use UNION.