In Oracle Database, the INTERSECT operator is used to create a compound query that returns the intersection of the results of the left and right SELECT statements. In other words, it combines two queries, but returns only those rows that are returned in both queries.

Example

Suppose we have the following tables:

SELECT * FROM Employees;
SELECT * FROM Customers;

Result:

EMPLOYEEID	EMPLOYEENAME
1	Bart
2	Jan
3	Ava
4	Rohit
5	Monish
6	Monish
7	Monish

CUSTOMERID	CUSTOMERNAME
1	Mia
2	Rohit
3	Peter
4	Ava
5	Monish
6	Monish

We can use the INTERSECT operator to return employees that are also customers:

SELECT EmployeeName FROM Employees
INTERSECT
SELECT CustomerName FROM Customers;

Result:

EMPLOYEENAME
Ava
Monish
Rohit

So it returns only those values that appear in both the Employees table and the Customers table.

Oracle Database’s implementation of the INTERSECT operator returns distinct rows only. We can see this in the above example. It returns just one row for Monish, even though there are multiple employees and multiple customers with that name.

Some RDBMSs allow us to include duplicates in the result by accepting an optional ALL keyword with the INTERSECT operator, but Oracle isn’t one of them (at least, not at the time of writing). Neither is SQLite.

An Alternative Query

It’s possible to get the same result without using the INTERSECT operator. For example, we could rewrite our first example to this:

SELECT 
    DISTINCT EmployeeName
FROM Employees e
WHERE EXISTS (SELECT CustomerName FROM Customers c
WHERE e.EmployeeName = c.CustomerName);

Result:

EMPLOYEENAME
Rohit
Ava
Monish

Some Things to Remember

Note that the expressions must match in number and must be in the same data type group. Therefore, we can’t do the following:

SELECT EmployeeName FROM Employees
INTERSECT
SELECT CustomerId, CustomerName FROM Customers;

Result:

ORA-01789: query block has incorrect number of result columns

Or this:

SELECT EmployeeName FROM Employees
INTERSECT
SELECT CustomerId FROM Customers;

Result:

ORA-01790: expression must have same datatype as corresponding expression

Although, we can use functions like TO_CHAR() to convert a column to a suitable data type group:

SELECT EmployeeName FROM Employees
INTERSECT
SELECT TO_CHAR(CustomerId) FROM Customers;

Result:

no data found

In this case, no data was found because none of the CustomerId values matched any of the EmployeeName values. But if they did, then we’d see the matches.