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.
create table PoolA ( CountryID int primarykey identity, Country Name varchar(20), ) create table PoolB ( CountryID int primarykey identity, CountryNamevarchar(20), ) insert into PoolA values ('India') insert into PoolA values ('Russia') insert into PoolA values ('Australia') insert into PoolB values ('Brazil') insert into PoolB values ('India') insert into PoolB values ('New Zealanad')
Execute the preceding queries in SQL Server.
We should get the following records on firing the select statements.
Select CountryName from PoolA Select CountryName from PoolB
Let us now use Union operator to check the output.
Select CountryName from PoolA Union Select CountryName from PoolB
Union Operator removes the duplicates from the result set.
Union All Operator
Select CountryName from PoolA UnionAll Select CountryName from PoolB
Union All Operator includes the duplicate elements too in the result set. Since, Union operator removes the duplicates from the result set so it is a bit slow as compared to Union All. Let us see this with the help of Estimated Executed 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.
Estimated Execution Plan using Union Operator :
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 :
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.
Select CountryID,CountryName from PoolA UnionAll Select CountryName from PoolB
These are the basic difference between Union and Union All Operator in SQL Server.
Read more articles on SQL Server :
转载本站任何文章请注明：转载至神刀安全网，谢谢神刀安全网 » Difference Between Union And Union All in SQL Server