Difference between Union and Union All

Introduction

Both Union and Union All are Used to Join Rows of two or more tables.

But the Union Or Union All  need the datatype same .

Lets Understand with the below example.

Difference-1

Union 

Union will combine table rows and will display the non repeated records, which are not duplicate.

See the below query and see the below output.

Query

select 'India'

union

select 'Pakistan'

union

select 'Australia'

union

select 'Pakistan'

union

select 'India'

Output

Australia

India

Pakistan

Union All

1.Union All will combine table rows and will display all records .

See the below query and see the below output.

Query

select 'India'

union all

select 'Pakistan'

union all

select 'Australia'

union all

select 'Pakistan'

union all

select 'India'

Output

India

Pakistan

Australia

Pakistan

India

You can see India and Pakistan repeated in Both the query but we get different result.

Difference-2

Union Use sort mechanism based upon its key while displaying the record where as Union All never use sort mechanism. It display the record as it is .

Lets Take an example .

Let create two table as below.

create table tblUnion1

(

id int,

Name varchar(20),

Country varchar(20)

)

create table tblUnion2

(

id int,

Name varchar(20),

Country varchar(20)

)

Lets Insert few records to both the tables as below.

insert into tblUnion1 values(1,'Name1','India')

insert into tblUnion1 values(2,'Name2','Pakistan')

insert into tblUnion1 values(3,'Name3','Australia')

insert into tblUnion2 values(1,'Name1','India')

insert into tblUnion2 values(2,'Name2','Pakistan')

insert into tblUnion2 values(3,'Name3','Australia')

insert into tblUnion2 values(5,'Name5','Australia')

insert into tblUnion2 values(4,'Name4','Srilanka')

Lets Execute the below query using UNION and UNION ALL and see what is going to happen.

select * from tblUnion1

union

select * from tblUnion2

Output for Union

1 Name1 India

2 Name2 Pakistan

3 Name3 Australia

4 Name4 Srilanka

5 Name5 Australia

select * from tblUnion1

union all

select * from tblUnion2

Output For Union All

1 Name1 India

2 Name2 Pakistan

3 Name3 Australia

1 Name1 India

2 Name2 Pakistan

3 Name3 Australia

5 Name5 Australia

4 Name4 Srilanka

See the above two output .

In Case of Union we got the data in a sorted order where in case of Union All we got the data as it is in the table.

Difference-3

Lets check the performance.

To check performance we can use Execution Plan for both Union and Union All.

To get execution plan along with the output you can goto Query->Include Actual Execute Plan

See the below Image.

Execution Plan Option

After Inclusion of execution Plan Lets execute the Below statements and see the execution Plan associated with it.

select * from tblUnion1

union

select * from tblUnion2

Execution Plan for Union

select * from tblUnion1

Union All

select * from tblUnion2

Execution Plan for Union All

See Both the Execution Plan. 

In 1st case(UNION) the execution plan taking 63% for sorting where as in 2nd case(UNION ALL) the execution do not have any sorting operation.

So this conclude that UNION ALL is faster than UNION.

Note

Lets note some point what we discussed above.

1. Union take Unique records where as Union All take all the records whether it is repeated or non repeated.
2. Union All is faster than Union as Union take extra time while sorting the record.
3. When we need unique records we use Union.
4. When we need duplicate records also then we will use Union All.
5. It is recommended to use Union All as much as possible compared to Union because of the better performance prospective.

For More Information Please refer below books....

Thanks........... 

Share: