Union, Except and Intersect Operators in Sql Server
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
Like it on Facebook, Tweet it or share this article on other bookmarking websites.