Union, Except and Intersect Operators in Sql Server

•Union Operator :

Union Operator is used to select related information from two or more table. But keep in mind, when you’ll use union operator, the selected columns should be of same data type.

Here is an example:
Let, there are two tables of a MNC Company – first one (from left) is Dept table and the next one is Details table.
  
Syntax:  
 
Select Column_Name from Table1
Union
Select Column_Name from Table2

Example:
Employee ID   Department               Name
0010              Finance                     Mary
0023              Marketing                  Allan
0045            Customer Service      Thomas
0167              Finance                     Victor
 
Employee ID Designation            Name
0010             Manager                Mary
0023             Branch Manager    Allan
0049             Developer              Mark
0167             Accountant           Victor
0901             Consultant              Lisa

 
Now use union operator to select Name and Employee ID:

Select Employee ID, Name from Dept
Union 
Select Employee ID, Name from Details     

Result:
Employee ID  Name
0010              Mary
0023              Allan
0045             Thomas
0167              Victor
0049              Mark
0901              Lisa

Remember, Union Operator only select distinct values. If you want to list all records from two or more tables use Union all instead of Union. Look at the sql query and corresponding output below:

Select Employee ID, Name from Dept
Union all 
Select Employee ID, Name from Details

Result:
Employee ID  Name
0010              Mary
0010              Mary
0023              Allan
0023              Allan
0045              Thomas
0049              Mark
0167              Victor
0167              Victor
0901              Lisa

•Except Operator:

Except operator is used to select all records from table1 which are not present in table2. More specifically, all the rows from the left side of except operator will be returned but it removes all of the rows from the result set that match the rows that are on the right side of the EXCEPT operator. The order and the datatype of the column should be same.

Syntax:
Select Column_Name from Table1
Except
Select Column_Name from Table2

Example:
Take the Dept and Details table. Write the following query.

Select Employee ID, Name from Details
Except
Select Employee ID, Name from Dept

Result:
  
Employee ID  Name
0049              Mark
0901              Lisa

•Intersect Operator:

Intersect operator is used to select the common records from both the left and right side query of intersect operator. But you should declare same number of columns in all queries. The order and the datatype of the column should be same.

Syntax:
Select Column_Name from Table1
Intersect
Select Column_Name from Table2

Example:
Take the Dept and Details table. Write the following query.

Select Employee ID, Name from Dept
Except
Select Employee ID, Name from Details

Result

Employee ID  Name
0010             Mary
0023             Allan
0167             Victor