The differences between UNION or UNION ALL operators is one of the most common interview question. By using the UNION or UNION ALL operators we can combine multiple result sets into one result set.
Lets consider we have the following two tables -
![]()
By observing the above tables first row is common in both.
UNION ALL:
UNION ALL operator is used to combine multiple result sets into one result set. This process is faster than UNION clause, why because it does not remove any duplicate rows and dint perform sort the rows. If you don’t want duplicate records you will need to use the UNION operator instead.
Example:
- Select * from Mas_Employee
- Union All
- Select * from Mas_Employee1
Output: By observing the output
UNION All operator didn't performs the sorting and not removing the duplicates rows.
![]()
UNION
UNION operator is similar to UNION ALL except it removes duplicates rows if any, and also Sort the rows.
Example:
- Select * from Mas_Employee
- Union
- Select * from Mas_Employee1
Output: By observing the output
UNION performs the sorting as well removes the duplicates rows.
![]()
Performance TIP:
Compared UNION ALL operator, UNION operator has the extra overhead of removing duplicate rows and sorting results. So, If we know that all the records returned by our query is unique from union then use UNION ALL operator instead of UNION Operator.
I hope you enjoyed it.