UNION and UNION all are both set operators. This means that they operate on sets of data that both combine two sets of data into singe result set.
Difference between UNION and UNION ALL
The difference of UNION and UNION ALL is how they treat the duplicates.
- UNION removes duplicate results from final result
- UNION ALL keeps any duplicates
To remember this easily is to note that the keyword “ALL” means to show all records. Because of this the UNION ALL will show the same number or more records than UNION.
What about performance? Performance differences between UNION vs UNION ALL
The UNION performs extra steps removing duplicates. The same thing is adding DISTINCT keywords. So it’s usually slower than just having the UNION ALL. So which one to use?
Unless you actually need to remove duplicates you should use UNION ALL. The reason is that this step is faster but if you do need to remove duplicates, then it is recommended to use UNION.
To see the examples check the below video: