Difference Between Union and Union All in SQL Server


union:-

  • union command remove the duplicates and selects only distinct values by default
  • UNION command must have the same number of columns and columns must also have similar data types.
  • union command more expensive than union all


Union all:-

  • Union all command Combines all the data
  • union all command does not remove duplicates and pull out all rows from all tables and combines them into a table.

Example –    I have created two tables and inserted some records in both tables:-

  • when we run the UNION & UNION ALL then we get below output

  • when we will compare the execution plan of UNION and UNION ALL then we will find that UNION is more expensive than UNION ALL.

 


 

Leave a Reply

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