SQLite INTERSECT Operator

In SQLite, the INTERSECT operator is used to create a compound SELECT statement 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 we only get values that appear in the Employees table that also appear in the Customers table.

SQLite’s implementation of the INTERSECT operator returns distinct rows only. So the above example returns just one row for Monish, even though there are multiple employees and multiple customers with that name.

Other RDBMSs allow us to include duplicates in the result by accepting an optional ALL keyword with their INTERSECT operator, but SQLite doesn’t (at least, not at the time of writing). Feel free to check SQLite’s documentation in case anything changes.

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
------------
Ava         
Rohit       
Monish