INTERSECT ​

INTERSECT keeps a row only when it occurs in both inputs. Repeated copies are removed. INTERSECT ALL is not supported.

Syntax ​

query INTERSECT query
[ORDER BY expression, ...]
[LIMIT count [OFFSET offset]]

Both inputs must return the same number of columns. Result column names come from the first query. Duplicate comparison applies to whole rows; NULL is equal to another NULL for this purpose.

A final ORDER BY or LIMIT applies to the combined result. Set operators at the same query level are evaluated from left to right. Put a nested query in a FROM subquery when a different grouping is needed.

Example ​

Combining two sets of identifiers ​

CREATE TABLE first_set (id INTEGER);
INSERT INTO first_set VALUES (1), (2), (2), (3);

CREATE TABLE second_set (id INTEGER);
INSERT INTO second_set VALUES (2), (4);

SELECT id FROM first_set
INTERSECT
SELECT id FROM second_set
ORDER BY id
FORMAT csv;

2

See UNION, INTERSECT and EXCEPT for the other set operations.