Difference between Union and Union All

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.

Let's 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 a different result.

Difference-2

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

Let's Take an example. Let create two tables as below.

create table tblUnion1
(
id int,
Name varchar(20),
Country varchar(20)
)
create table tblUnion2
(
id int,
Name varchar(20),
Country varchar(20)
)

Let's 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')

Let's 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.