Following are the constraints that are associated with UNION and UNION ALL operator
Ø The tables should contains the same number of columns
Ø Columns in the tables must be of the same data type
Ø Order by clause should be applied to the final result set
Ø Column name for the result set would be of the first query mentioned
Union
UNION combines the result set of two or more queries into a single result set. This result set includes all the rows that belong to all queries in the UNION. And it is providing distinct result set.
Let’s see the example
Creating two table
create table Emp (ID int, Name Varchar(50) )
create table Emp1 (ID int, Name Varchar(50) )
|
Now I am inserting the some records.
insert into Emp (ID,Name) values (1,'Bagesh')
insert into Emp (ID,Name) values (2,'Kumar')
insert into Emp (ID,Name) values (3,'singh')
insert into Emp1 (ID,Name) values (1,'Bagesh')
insert into Emp1 (ID,Name) values (2,'Kumar')
insert into Emp1 (ID,Name) values (3,'singh')
insert into Emp1 (ID,Name) values (4,'Rajesh')
insert into Emp1 (ID,Name) values (5,'Mahesh')
insert into Emp1 (ID,Name) values (6,'Ganesh')
|
UNION removes duplicate rows, UNION have to perform distinct sort to remove duplicates.
The distinct sort operation happening in UNION is time consuming. Let’s see the Estimated Execution Plan in SQL Server Management Studio (SSMS).
Click on the Estimated Execution plan icon.
See here 63% uses for shorting.
Both tables have same number of columns and data types. If not then it will be through error.
Union All
UNION ALL combines the result set of two or more queries into a single result set. This result set includes all the rows that belong to all queries in the UNION ALL. And it is providing all records in a result set.
It is faster than the UNION because it is not sorting the records. See the Execution plan
There is no cost for the sorting so it is faster than the UNION.
UNION and UNION ALL with TEXT Data Type
When we use a UNION for the TEXT type columns, SQL Server throws a runtime error.
create table Emp2 (ID int, DescT text )
create table Emp3 (ID int, DescT text )
|
Inserting some records
insert into Emp2 (ID, DescT) values (1,'This is Bagesh')
insert into Emp2 (ID, DescT) values (2,'This is Kumar')
insert into Emp2 (ID, DescT) values (3,'This is singh')
insert into Emp3 (ID, DescT) values (1,'This is Bagesh')
insert into Emp3 (ID, DescT) values (4,'This is Rajesh')
insert into Emp3 (ID, DescT) values (5,'this is Mahesh')
|
When we use UNION we will get the below error
The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Getting this error because UNION sort the record but it is not able to sort the TEXT data type.
Now see with the UNION ALL
Summary
UNION
|
UNION ALL
|
UNION removes duplicate rows.
|
“UNION ALL” does not remove the duplicate row. It returns all from all queries.
|
UNION uses a distinct sort
|
“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
|
UNION cannot work with a column that has a TEXT data type.
|
UNION ALL can work with all data type columns.
|
It is slower then Union all
|
It is faster than Union
|
