SQL Server INTERSECT Operator

In SQL Server, the INTERSECT operator is used to fetch the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Syntax:

SELECT expression1, expression2, ... expression_n  

FROM tables  

[WHERE conditions]  

INTERSECT  

SELECT expression1, expression2, ... expression_n  

FROM tables  

[WHERE conditions];

Parameter explanation

expression1, expression2, … expression_n: expressions specify the columns or calculations that you want to compare between the two SELECT statements. There is not necessary to have the same fields in each of the SELECT statements, but the corresponding columns must be similar data types in both tables.

tables: It specifies the tables that you want to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions: It is optional condition. It specifies the conditions that must be met for the records to be selected.

Image representation:

Note: The overlapped blue field specifies the intersect data.

Mandatory conditions for INTERSECT operation

The number of expressions in both SELECT statements must be same.
The corresponding columns in each of the SELECT statements must have similar data types.
The INTERSECT operator fetch only common records the SELECT statements.

INTERSECT operator with single expression

Example:

SELECT name  

FROM [javatpoint].[dbo].[Employees]  

INTERSECT  

SELECT name  

FROM [javatpoint].[dbo].[Employee2];

Output:

INTERSECT operator with single expressions

SELECT id, name, salary  

FROM [javatpoint].[dbo].[Employees]  

WHERE salary >= 15000  

INTERSECT  

SELECT id, name, salary  

FROM [javatpoint].[dbo].[Employee2]