INTERSECT

The SQL INTERSECT operator is used to retrieve the common records from two or more SELECT statements. The INTERSECT operator returns only those records that appear in all the SELECT statements specified in the query.

Syntax

The syntax for the SQL INTERSECT operator is as follows:

SELECT column1, column2, ... FROM table1
INTERSECT
SELECT column1, column2, ... FROM table2

In this syntax, the SELECT statements can retrieve data from the same or different tables. The INTERSECT operator requires that the SELECT statements retrieve the same number of columns with compatible data types.

Example

Let’s look at an example to understand how the SQL INTERSECT operator works. Suppose we have two tables, Employees and Managers, with the following data:

Employees Table:

EmployeeID	FirstName	LastName
1	John	Smith
2	Jane	Doe
3	Mark	Johnson
4	Sarah	Lee
5	James	Wilson

Managers Table:

ManagerID	FirstName	LastName
1	John	Smith
2	Jane	Doe
6	Anna	Lee

create table Employees
(
EmployeeID int, 
FirstName varchar(250), 
LastName varchar(250)
);
insert into Employees(EmployeeID, FirstName, LastName) values
(1,'John','Smith'), (2,'Jane','Doe'), (3,'Mark','Johnson'), 
(4,'Sarah','Lee'), (5,'James','Wilson');

create table Managers
(
ManagerID int, 
FirstName varchar(250), 
LastName varchar(250)
);
insert into Managers(ManagerID, FirstName, LastName) values
(1,'John','Smith'), (2,'Jane','Doe'), (6,'Anna','Lee');

We can use the following SQL query to retrieve the common records from both tables:

SELECT FirstName, LastName FROM Employees
INTERSECT
SELECT FirstName, LastName FROM Managers

This query will return the following result:

FirstName	LastName
John	Smith
Jane	Doe

In this example, the INTERSECT operator has retrieved the common records from the Employees and Managers tables based on the FirstName and LastName columns. The records with the values John Smith and Jane Doe appear in both tables and are therefore returned by the query. The record with the value Anna Lee appears only in the Managers table and is not returned by the query.