Tip:
Highlight text to annotate it
X
In this video my papa will explain you
what is the difference between UNION and UNION All
I know
This is a tough question
in the interview
That was my kid Sanjana
As she has said that in this video we are going to talk about
what is the difference between
Union and Union All
You can see on your screen there are two tables here one is called as the Ancillary Table and the other one is called as a Main Product Table
The Ancillary table has a product name and the amount
while the main product also has a product name and the amount
Select *
from
Ancillary
Go
select
*
from
Main product
and execute
When I fire both this select statements it is creating two outputs here
The top output is displaying the results of the Ancillary table
and the bottom output displaying the
results of the Main product table
Combine both these records
in one record set in other words I don't want to display them in this way in different windows
I would like to display them in
one
selected row and in one record set
That's done by using UNION
Go and say union
and if you select
You can see that
Both the records of the Ancillary table as well as the Main product table has been combined into one record set
From the Ancillary table we have Jam and Egg
and from the Main product table we have White Bread and Brown Bread
All the records are combined into one record set
Basic use of the Union is
to combine
two select statements
and
display them as one record set
Definitely when you write select statement
the number of columns as well as the data types of both the select statement should match
But there are Eggs where is the other egg
Kids are smarter nowadays
and
Sanjana has caught me right
She mean to say that your Ancillary table has a Egg record your Main product table also has a Egg record. In other words this 3
plus 2 should display 5
But its only displaying
4 records
What happened to the other Egg record?
When we use Union
it does not display duplicate values
In other words
here the value of Egg is 20
and in the Main product the value of Egg is 20
It has said that this is a duplicate value and it just display one value
If you want to display also the duplicate value you have to say union
all
So that my kid gets all the eggs
If I say execute now
We have one egg record here
and the other Egg record here
Total we have 5 records
The difference between UNION All and UNION is
UNION All displays
duplicate values
while UNION
does not display duplicate value. In case you finds a duplicate values
it will just
take the distinct out of it
Next time when you will go to the SQL Server Interview
and if somebody asks you what is the difference between UNION and UNION All I am sure now you are set right because
even my kid understood what is the difference between UNION and UNION All
you should also understand well
Thank you so much