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.
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.
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:
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:
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.