The Difference Between UNION and UNION ALL

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.

[cc lang=”sql”]
— create the table and populate sample data
SELECT
name = ‘derek’
,detail = ‘this is record 1’
INTO #faba

— perform the UNION on the same table
SELECT *
FROM #faba
UNION
SELECT *
FROM #faba

— perform the UNION ALL on the same table
SELECT *
FROM #faba
UNION ALL
SELECT *
FROM #faba
[/cc]

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.