SQL Tip: Union vs Union All

A co-worker gave me a SQL tip today.

TIP: Whenever possible, use UNION ALL, instead of UNION, because it is faster.

This is a great tip and here's why. UNION eliminates all duplicate rows and sorting results. This requires that it create a temp table, storing all the records and sorting them before generating the result. So its not the most efficient way of doing things. A potential issue with using UNION is the danger of loading the tempdb database with a huge temptable.

UNION ALL, on the other hand, doesn't eliminate duplicates. It just performs the first select and then appends the second select onto the first result set. There is no temp table or sorting involved. This is much more efficient. If you don't expect duplicate data, then this is the way to go.