TSQL - Set operations in SQL Server

In SQL Server, Set operations allow the results of multiple queries to be combined into a single result set. Set operators include UNION, UNION ALL, INTERSECT, and EXCEPT. Before using the set operations in SQL Server, we should keep the following things in our mind -

1. These set operations are design to work around more than two datasets or queries

2. Each dataset or query must have the same number of columns

3. Each column must have compatible data types,

4. Column names for the final result set are taken from the first query or datasets

Union - This operator is design to merge more than two datasets and it eliminates duplicate rows if any. It gives unique record from all data set. 

Union all - This operator to design merge more than two datasets and does not eliminate duplicate rows if any. It gives all  records from all data set.

Intersect - It will show when rows are identical and not include rows which are different. It will remove duplicate rows from the final result set.

EXCEPT - It takes the distinct rows of one query and returns the rows that do not appear in a second result set. The EXCEPT ALL operator does not remove duplicates. For purposes of row elimination and duplicate removal, the EXCEPT operator does not distinguish between NULLs.