UrbanPro
true

Learn Microsoft Azure Training from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

Difference between Union and Union All

Abinash Hota
05/07/2019 0 0

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.

0 Dislike
Follow 2

Please Enter a comment

Submit

Other Lessons for You

Difference between App services,Cloud Service and Virtual Machine in Microsoft Azure
App Services :When you want to deploy your application to Azure IIS conatainers without requiring any control,web apps should be preferred.It's a part of Azure Paas.1) When you need IIS.2) Satisfy with...

OAuth Authentication (without using ADAL) to Dynamics 365 using Azure Apps
Here I am going to show without using ADAL(active directory authentication library) how to get the authentication token and how to connect to CRM from a standalone HTML Page using the web-API. I will...

Azure Storage Gen1 VS Azure Storage Gen1
We have azure storage from the beginning now Microsoft Azure introduce azure Storage Gen2, here we have BLOB+Data Lake Gen2 is combined But we should remember here for few features of data lake gen...
M

Azure Course Content
Microsoft Azure Course Content Lesson 1: Introduction to Azure Overview of On-premise infrastructure The transition from On-premise to datacenter housing & managed data centres and finally...
X

Looking for Microsoft Azure Training Classes?

The best tutors for Microsoft Azure Training Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Microsoft Azure Training with the Best Tutors

The best Tutors for Microsoft Azure Training Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more