Post
Ask Question

Difference Between Union And Union All in SQL Server

Union and Union All operators are used to combine the result sets of two or more Select queries. But there is a difference between these two operators. Let us see this with the help of an example in SQL Server.

We will create two tables first. Using these two operators we will compare the output of both the queries. We will create a duplicate entry in the second table and see how these operator works.

Write the following scripts and execute in appropriate database.

  1. create table PoolA  
  2. (  
  3.   CountryID int primarykey identity,  
  4.   Country Name varchar(20),  
  5. )  
  6.   
  7. create table PoolB  
  8. (  
  9.   CountryID int primarykey identity,  
  10.   CountryNamevarchar(20),  
  11. )  
  12.   
  13. insert into PoolA values ('India')  
  14. insert into PoolA values ('Russia')  
  15. insert into PoolA values ('Australia')  
  16.   
  17. insert into PoolB values ('Brazil')  
  18. insert into PoolB values ('India')  
  19. insert into PoolB values ('New Zealanad')  
Execute the preceding queries in SQL Server.

queries

We should get the following records on firing the select statements.
  1. Select CountryName from PoolA  
  2.   
  3. Select CountryName from PoolB  

records

Union Operator

Let us now use Union operator to check the output.
  1. Select CountryName from PoolA  
  2.   
  3. Union  
  4.   
  5. Select CountryName from PoolB  

output

Union Operator removes the duplicates from the result set.

Union All Operator
  1. Select CountryName from PoolA  
  2.   
  3. UnionAll  
  4.   
  5. Select CountryName from PoolB 

UnionAll

Union All Operator includes the duplicate elements too in the result set. Since Union operator removes the duplicates from the result set it is a bit slow as compared to Union All. Let us see this with the help of Estimated Executed Plan.

Checking Performance using Estimated Execution Plan

We can either use Ctrl + L to see Estimated Execution Plan or go to Query, then Display Estimated Executed Plan in the SQL Server Menu. Refer the following image.

Display

Estimated Execution Plan using Union Operator :

Execution

In the above result Distinct Sort takes 63% of the time in the execution. That is why it is slow.

Estimated Execution Plan using Union All :

Execution

Union All doesn’t contain distinct sort. So it is faster than Union Operator.

One important difference worth mentioning is that the sequence of the columns should be same in both the queries otherwise it will throw an error.
  1. Select CountryID,CountryName from PoolA  
  2.   
  3. UnionAll  
  4.   
  5. Select CountryName from PoolB 

UnionAll

These are the basic differences between Union and Union All Operator in SQL Server.
Read more articles on SQL Server:

 


Similar Articles