The difference between UNION and UNION ALL Operator :

There is a difference between union and a union All operator which users are not aware of:

The union all operator will result in a set which will not eliminate the duplicates from the resulting set , means it will return the duplicate rows if residing in the set .

On the other hand union operator will eliminate the duplicates from the resulting set .

Whenever , you see a ALL keyword with union or intersect or except operators , it means it will give all the results including the duplicate records in the set .