By Navratan Verma
Union Operator is used to combine multiple result sets into one and also removes any duplicates rows that exist. it gives distinct values across all columns in the result set.
UNION ALL OPERATOR: is used to combine multiple result sets into one, but it does not remove any duplicate rows. Since this does not remove duplicate rows the cost is less and performs faster, but if you don’t want duplicate records you will need to use the UNION operator instead.
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.
Download the script used here : Union
Here is the execution plan results First query is uses union and second union all.