Hello friends, today we are going talk about the difference between Union and Union all operators. Before comparing the difference, first we need to understand what is the use of these two operators and what are the limitations around using them.
UNION: – Union operator is used to combine two result sets having same no of columns. If there will be any duplicate record, it will be removed from the combined result set.
UNION All: -Union all operator is also used to combine two result sets having same no of columns. If there will be any duplicate record, it will not be removed from the combined result set.
Constraints for using UNION and UNION ALL operators: – Below are few constraints for using these two operators.
o All the queries which are going to be combined needs to have same no of columns.
o We cannot use Order by clause in individual query, if we want to apply order by clause it should be on combined result set.
o All columns from all the queries should be compatible with each other.
o Column names of the combined result set would be the column name used in the first query.
Performance Factor:- If we compare these two operators in terms of performance, Union ALL operator is fast, and the reason behind this fact is that, UNION operator has a overhead of removing duplicate records from the combined result set.
Let’s take an example to see the results, we will create two tables Employee and Dept and insert few rows into it, using below script.
I have inserted one duplicate record in Employee table, to check if it is being removed from the combined result set or not while using Union Operator.
UNION ALL Syntax:-
select * from Employee
In the above screenshot, we can see that both duplicate records are present in the combined result set.
Note:-In the above result set, As stated above that combined result set’s column names are taken from the first query( we can see that the column names has been taken from the first query(Eid, Name)).
1,110 total views, 2 views today