`INTERSECT` clause

The INTERSECT expression returns all rows contained in the results of both the first and the second query. In other words, this expression calculates the intersection of the results of the two queries.

To be able to compute the intersection for two queries, the queries must be compatible. This means that they must have the same number of columns and the corresponding columns must be of compatible types.

Syntax

SELECT ...
INTERSECT
SELECT ...

Examples

Let’s output the list of countries that occur in the country column in both the capitals table and the population table:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
CREATE TABLE population (country VARCHAR, population_mil BIGINT);

INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');
INSERT INTO population VALUES
    ('Russia', 143),
    ('Spain', 48),
    ('Brazil', 211);

SELECT country FROM capitals
INTERSECT
SELECT country FROM population;

+---------+
| country |
+---------+
| Russia  |
+---------+
| Spain   |
+---------+

Let’s use the functions unnest and generate_series and compute the intersection of two queries — for numbers from 1 to 10 and for numbers from 10 to 20:

SELECT unnest(generate_series(1, 10)) as numbers
INTERSECT
SELECT unnest(generate_series(10, 20)) as numbers;

+---------+
| numbers |
+---------+
| 10      |
+---------+