How INTERSECT Works in PostgreSQL

In PostgreSQL, the INTERSECT operator combines two queries, but returns only those rows that are returned in both queries.

Syntax

The syntax goes like this:

query1 INTERSECT [ALL] query2

Duplicates are eliminated unless INTERSECT ALL is used.

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

So we only get values that appear in the Employees table that also appear in the Customers table.

By default, it returns distinct rows, so only one row is returned for Monish, even though there are multiple employees and multiple customers with that name.

It’s the same as doing this:

SELECT EmployeeName FROM Employees
INTERSECT DISTINCT
SELECT CustomerName FROM Customers;

Result:

 employeename 
--------------
 Ava
 Rohit
 Monish

So we get the same result that we got when we didn’t include the DISTINCT operator.

Include Duplicates

As mentioned, the INTERSECT operator returns only distinct values by default. But we can add the ALL keyword to include duplicates:

SELECT EmployeeName FROM Employees
INTERSECT ALL
SELECT CustomerName FROM Customers;

Result:

 employeename 
--------------
 Ava
 Rohit
 Monish
 Monish

This time we got four rows, instead of the three that we got in our first example.

We can see that two rows containing the name Monish were returned instead of just one like in our first example. There are actually three customers called Monish, but only two employees with that name. Therefore, the operation only intersects two of them.

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