Difference between Union and Union all Operator

Hello friends, today we are going talk about the difference between Union and Union all operators. Before comparing the difference, first we need to understand what is the use of these two operators and what are the limitations around using them.

UNION: - Union operator is used to combine two result sets having same no of columns. If there will be any duplicate record, it will be removed from the combined result set.

UNION All: - Union all operator is also used to combine two result sets having same no of columns. If there will be any duplicate record, it will not be removed from the combined result set.

Constraints for using UNION and UNION ALL operators: - Below are few constraints for using these two operators.

• All the queries which are going to be combined needs to have same no of columns.
• We cannot use Order by clause in individual query, if we want to apply order by clause it should be on combined result set.
• All columns from all the queries should be compatible with each other.
• Column names of the combined result set would be the column name used in the first query.

Performance Factor:- If we compare these two operators in terms of performance, Union ALL operator is fast, and the reason behind this fact is that, UNION operator has a overhead of removing duplicate records from the combined result set.

Let’s take an example to see the results, we will create two tables Employee and Dept and insert few rows into it, using below script.

Create table Dept(Emp_id int,Dept char(20))

insert into Dept values(101,'IT')

insert into Dept values(102,'Account')

insert into Dept values(103,'HR')

Create table Employee(Eid int,Name Varchar(20))

insert into Employee values(101,'Neeraj')

insert into Employee values(102,'Neha')

insert into Employee values(103,'Rachit')

insert into Employee values(101,'Neeraj')

insert into Employee values(104,'Ankit')

I have inserted one duplicate record in Employee table, to check if it is being removed from the combined result set or not  while using Union Operator.

UNION Syntax:-

select  * from Employee

union

select * from dept

In the above screenshot, we can see that the duplicate record has been removed from the combined result set. We had two records in Employee table with Eid=101, in the above result set, only one record is showing.

UNION ALL Syntax:- 

select  * from Employee

UNION ALL

select * from dept

In the above screenshot, we can see that both duplicate records are present in the combined result set.

Note:- In the above result set, As stated above that combined result set’s column names are taken from the first query( we can see that  the column names has been taken from the first query(Eid, Name)).