Union Vs. Union All

The Union and Union All operators combine the results of two SQL queries. Union All merges every row, while Union throws out duplicate rows. It turns out that Union actually performs the equivalent of distinct sort behind the scene.

As a simple test, I ran 3 separate queries: (1) using Union All, (2) using Union, and (3) using Union All and distinct sort to generate the same results as query 2. For query 3, the order by clause is actually not necessary, as select distinct also automatically sorts in ascending order. Below is the SQL Server execution plan for each query.

Queries 2 and 3 are executed identically. As expected, both are much more expensive than query 1. So stick with Union All whenever possible.

Related posts: