Union and Union All, both are used to select data from one or more than one tables but still they have some differences. For Example, Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.


Suppose we have a table called tbl_Manager and the structure of this table is given below:-

Now Suppose we insert the data into the table with the help of the query given below:-

Now Suppose, we want to select the “Managername” from the table who work in the department “Technology” and “Mobile development”.


First, we try to get the result with the help of Union.In this case the query will be given below:-

Result :-

Now if we use the Union All instead of union to selet the managername from the table who work in the department “Technology” and “Mobile development”, we get the following result:-

select Managername from tbl_Manager where departmentname='Technology'
union all
select Managername from tbl_Manager where departmentname='Mobile development'



Result:-

By analyzing the two results, one can easily find out that the Union all allowed dublication of the data and Union do not allows the duplicate data to appear.


The second difference between Union and Union All can be given on the basic of their uses. Both Union and Union All  can be used to insert multiple rows in the table in a single query. You can see the use of the Union All in the insert statement, which we have used to insert the data into the table tbl_Manager.But in case where we required to insert multiple rows containing duplicate values , we can’t use Union for this purpose. Also the use of Union will increase the cost of execution plan for the insert query as compared to Union all..


Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.


Summary
Both Union and Union All are used to select the data from the tables but we should use Union All unless it is required to fetch only the distinct data since the use of Union will increase the cost of the execution of the query.