What is the difference between UNION and UNION ALL ?

This is the common interview question for Freshers not only for database developers, but also for programming developers.


Let us create two simple tables form those tables we can easily find the difference.

Create table blog1(id int, name varchar(100),phno nvarchar(20))
insert values into blog1(1,'sai',12345)
insert values into blog1(2,'sandeep',123456)

Create table blog2(id int, name varchar(100),phno nvarchar(20))
insert values into blog1(1,'kalyan',12345)
insert values into blog1(2,'sandeep',123456)


The difference between UNION and UNION ALL is that UNION does not take duplicate values where as UNION ALL will accept duplicate values.


UNION :


Now execute the following query by using UNION

select * from blog1
union
select * from blog2

Now the result will be as follows. Where duplicates will be removed.

id	name	phno
1	kalyan	12345
1	sai	12345
2	sandeep	123456

UNION ALL: 

Now execute the following query by using UNION ALL

select * from blog1
union all
select * from blog2

Now the result will be as follows. Where duplicates will be added.

id	name	phno
1	sai	12345
2	sandeep	123456
1	kalyan	12345
2	sandeep	123456

Here you can check the performance by using execution plan.

Difference between UNION and UNION ALL

• The union does not take duplicates where as Union All will take duplicates.
• Union follows a distinct sort that's why Union All is faster than the Union.
• The Text datatype does not support to the Union, where as Union All will support all the data types.

Note:

While performing Union and Union All the number of  columns should be same in both tables and data types should also be same.