神刀安全网

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.

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.

Difference Between Union And Union All in SQL Server

We should get the following records on firing the select statements.

Select CountryName from PoolA      Select CountryName from PoolB   

Difference Between Union And Union All in SQL Server

Union Operator

Let us now use Union operator to check the output.

Select CountryName from PoolA      Union      Select CountryName from PoolB   

Difference Between Union And Union All in SQL Server

Union Operator removes the duplicates from the result set.

Union All Operator

Select CountryName from PoolA      UnionAll      Select CountryName from PoolB  

Difference Between Union And Union All in SQL Server

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.

Difference Between Union And Union All in SQL Server

Estimated Execution Plan using Union Operator :

Difference Between Union And Union All in SQL Server

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 :

Difference Between Union And Union All in SQL Server

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  

Difference Between Union And Union All in SQL Server

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

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮