Difference Between Union vs. Union All – Optimal Performance Comparison

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

DECLARE @Table1 TABLE (ColDetail VARCHAR(10))INSERT INTO @Table1SELECT 'First'UNION ALLSELECT 'Second'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fourth'UNION ALLSELECT 'Fifth'/* Declare Second Table */DECLARE @Table2 TABLE (ColDetail VARCHAR(10))INSERT INTO @Table2SELECT 'First'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fifth'/* Check the data using SELECT */SELECT *FROM @Table1SELECT *FROM @Table2/* UNION ALL */SELECT *FROM @Table1UNION ALLSELECT *FROM @Table2/* UNION */SELECT *FROM @Table1UNION
SELECT *FROM @Table2GO

In our example we have two tables: @Table1 and @Table2.

Now let us run UNION ALL and UNION together and see the resultset as well as Execution Plan compared to complete set of query. You can always turn on actual execution plan using CTRL+M.

We can see from the resultset of UNION ALL that it returns everything from both the table but from UNION it is very clear that only DISTINCT rows from both the table is only retrieved.

Additionally, when comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.