Difference between UNION and UNION ALL

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