Set operators are used to combine two or more result set into one data set or in some cases, set operators are used to pick-up unique data from multiple data-sets . It is always necessary while working in data warehouse scenario that means in large volume of data . In SSIS, we have UNION ALL Transformation in the data flow environment to combine multiple data-sets.

But in transact SQL, we have multiple set operators to perform data merging operations.

SET operators in SQL Server (T-SQL) are:

UNION

UNION ALL

EXCEPT

INTERSECT

UNION OR UNION ALL

Union or Union All set operators are used to combine two or more similar dataset. It means, all columns in each dataset must be equal and in same order and It should have sane data type.

UNION ALL – This set operator merges multiple datasets and it includes duplicate rows if any.

UNION – This set operator merges multiple datasets and it eliminates duplicate rows if any. It give unique record from all data set.