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.
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
Hi Vivek,
I read this article and try second difference i.e. '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.We can’t use Union for this purpose.'
I'm able to insert using both UNION and UNION ALL.
What I try is:
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
and
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,3
UNION
SELECT 'Second' ,4
with both as Primary key constraints and no constraint on SecondCol.
Please rectify me where I'm wrong.
Thnx in advance
Tulika
Hi Tulika,
Thanks for your valuable comments. We can use Union as well as Union All in the insert statement but we can not use Union in case, when we required to insert duplicate rows in the table. For Example if we write the following Insert statement
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,3
UNION
SELECT 'Second' ,4
UNION
SELECT 'Second' ,4
It will only insert 2 rows instead of 3 rows. But if we use Union all in place of Union, it will insert all the three rows.
Also the use of Union will increase the cost of execution plan since it have to perform two functions Select and then Distinct function. Therefore use of Union all is preferred over Union in select statement.
I will also rectified this point in my above article.
Thanks vivek for explaining in such a descriptive manner….
Choudhary Nafees