PostgreSQL INTERSECT

This article describes how to use the INTERSECT operator to compute the intersection of two sets.

In PostgreSQL, INTERSECT is a set operator that returns the intersection of two sets. That is, it returns those rows that are in both result sets.

Other set operators are: UNION and EXCEPT.

PostgreSQL INTERSECT syntax

To compute the intersection of two result sets, use the using INTERSECT operator:

SELECT_statement_1
INTERSECT
SELECT_statement_2
[ORDER BY ...];

Or you can compute the intersection of multiple result sets at the same time, like this:

SELECT_statement_1
INTERSECT
SELECT_statement_2
INTERSECT
SELECT_statement_3
INTERSECT
...
[ORDER BY ...];

Here:

  • The SELECT_statement_N are independent SELECT statements.
  • All result sets participating in the INTERSECT operation should have the same columns, and the data types and order of the columns should be the same.
  • The ORDER BY clause is used to sort the final result and it is optional.

PostgreSQL INTERSECT Examples

In this example, we will use the generate_series() function to generate two result sets.

We need to use two result sets, the following is the first result set:

SELECT generate_series(1, 5);

 generate_series
-----------------
               1
               2
               3
               4
               5

The following is the second result set:

SELECT generate_series(3, 6);

 generate_series
-----------------
               3
               4
               5
               6

Now, you can intersect the two result sets using the INTERSECT operate:

SELECT generate_series(1, 5)
INTERSECT
SELECT generate_series(3, 6)
ORDER BY generate_series;

 generate_series
-----------------
               3
               4
               5
(3 rows)

As it turns out, the INTERSECT operator returns the rows that are in the both sets.

Conclusion

In PostgreSQL, INTERSECT is used to compute the intersection of two result sets.