INTERSECT in PostgreSQL

The INTERSECT operator returns only the rows that appear in the result sets of both SELECT queries. It is the set-theoretic intersection — any row not present in both results is excluded. Like UNION, INTERSECT automatically removes duplicate rows unless you use INTERSECT ALL.

Syntax

SELECT select_list FROM table_a
INTERSECT
SELECT select_list FROM table_b;

Both queries must return the same number of columns in the same order with compatible data types. To sort the intersection:

SELECT select_list FROM table_a
INTERSECT
SELECT select_list FROM table_b
ORDER BY sort_expression;

Practical Example

A conference organizer tracks workshop sign-ups and keynote sign-ups separately. Find attendees registered for both:

CREATE TABLE workshop_signups (
  email      VARCHAR NOT NULL,
  full_name  VARCHAR NOT NULL
);

CREATE TABLE keynote_signups (
  email      VARCHAR NOT NULL,
  full_name  VARCHAR NOT NULL
);

INSERT INTO workshop_signups (email, full_name) VALUES
  ('alice@example',  'Alice Nguyen'),
  ('bob@example',    'Bob Okafor'),
  ('clara@example',  'Clara Hoffman'),
  ('diana@example',  'Diana Park');

INSERT INTO keynote_signups (email, full_name) VALUES
  ('alice@example',  'Alice Nguyen'),
  ('clara@example',  'Clara Hoffman'),
  ('evan@example',   'Evan Torres');

Find attendees registered for both sessions:

SELECT email, full_name FROM workshop_signups
INTERSECT
SELECT email, full_name FROM keynote_signups
ORDER BY full_name;

Result:

        email        |  full_name
---------------------+---------------
 alice@example   | Alice Nguyen
 clara@example   | Clara Hoffman

Bob, Diana, and Evan appear in only one table and are excluded.

INTERSECT ALL example

If duplicate rows exist and you want to preserve them proportionally:

-- Insert a duplicate workshop registration for Alice
INSERT INTO workshop_signups (email, full_name)
VALUES ('alice@example', 'Alice Nguyen');

-- INTERSECT: still returns Alice exactly once
SELECT email, full_name FROM workshop_signups
INTERSECT
SELECT email, full_name FROM keynote_signups;

-- INTERSECT ALL: returns Alice as many times as she appears
-- in the smaller count (1 time in keynote_signups)
SELECT email, full_name FROM workshop_signups
INTERSECT ALL
SELECT email, full_name FROM keynote_signups;

Chaining multiple INTERSECT operators

Find products listed in all three sales channels:

SELECT product_id FROM channel_web
INTERSECT
SELECT product_id FROM channel_retail
INTERSECT
SELECT product_id FROM channel_wholesale
ORDER BY product_id;

Each INTERSECT narrows the result set to rows present in all listed queries.

INTERSECT vs INNER JOIN

Both can find common records but work differently:

Prefer INTERSECT when both queries have the same structure and you want exact row matching. Use INNER JOIN when you need to pull additional columns or join on a specific key.

Testing with Vela

Use Vela’s database branching to validate INTERSECT logic before running it against production data. Create a branch from production, populate test data that includes both overlapping and non-overlapping rows, and verify that your INTERSECT query returns the expected subset. This is especially useful when INTERSECT is part of a data-reconciliation pipeline where correctness is critical.

Production Tips

• INTERSECT deduplicates the result, which requires hashing or sorting both result sets. For large tables, an INNER JOIN on an indexed key column is often faster.
• Column names in the output come from the first SELECT. Use aliases in the first query to control the column names in the result.
• All set operators (UNION, INTERSECT, EXCEPT) have equal precedence and are evaluated left to right. Use parentheses to control order when mixing them in a single statement.
• INTERSECT ALL is rarely needed but is the correct tool when row frequency matters — for example, finding SKUs that appear the same number of times in two inventory snapshots.
• Use EXPLAIN ANALYZE to inspect how PostgreSQL executes INTERSECT. It typically appears as a HashSetOp Intersect or SetOp node in the plan.