Often many ask, what is the difference between union and union all?
Union operator combines multiple resultsets into one resultset. Union also removes the duplicates and returns a sorted result set.
Union All operator combine multiple resultsets into one resultset, but it does not eliminate the duplicate rows. Since the duplicate records are preserved Union all runs faster than union and this does not return a sorted resultset.
As multiple resultsets are combined, indeed the Union and Union All should abide to few basic ground rules
- The resultsets should be identical in terms of number of columns and compatible data types
- The column names of the combined resultset will be same as the column names in the first resultset
Consider the recordsets that are mutually exclusive, Run the below queries in Management studio to create the temporary tables and populate those with some data.
USE DEMODB; CREATE TABLE #temp1 (id INT); CREATE TABLE #temp2 (id INT); GO INSERT INTO #temp1 SELECT number FROM master..spt_values WHERE type = 'p' AND number BETWEEN 1 AND 499; INSERT INTO #temp2 SELECT number FROM master..spt_values WHERE type = 'p' AND number BETWEEN 500 AND 1000;
Now run the below query in the management studio and capture the execution plan
SELECT *FROM #temp2 UNION SELECT *FROM #temp1 SELECT *FROM #temp2 UNION ALL SELECT *FROM #temp1
From the execution plan it is evident that Union is costlier than union all (using 82% of total batch time). You can notice that the union does a distinct sort to get rid of the duplicate records. In case of union all it just combines the resultset.
So if you don’t care about duplicates in the final resultset or know in hand that the resultsets you combine are mutually exclusive, it is better to use union all as Union all would yield better performance.