What is the difference between UNION and UNION All ( SQl server )?
Articles,  Blog

What is the difference between UNION and UNION All ( SQl server )?


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

46 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *