INTERSECT
INTERSECT returns only the rows that appear in the result sets of both of two queries, effectively computing the common rows between them.
Overview
INTERSECT is a set operation that returns rows present in both of two SELECT queries' result sets. Like UNION, the two queries must return the same number of columns with compatible types, and column names in the output come from the first query.
Copy code
SELECT customer_id FROM web_signups
INTERSECT
SELECT customer_id FROM email_subscribers;
-- customers who both signed up on the web and subscribed to email
INTERSECT vs INNER JOIN
INTERSECT compares entire rows (or the full set of selected columns) for equality, whereas a JOIN matches on specific key columns and can return additional columns from both sides. For a simple "which IDs appear in both lists" check, INTERSECT is often more readable than a JOIN; for combining columns from both sides, a JOIN is the right tool.
Duplicate handling: INTERSECT vs INTERSECT ALL
Standard INTERSECT uses set semantics -- duplicates are eliminated, and each distinct row appears at most once in the output, regardless of how many times it appeared in either input. DuckDB also supports INTERSECT ALL, which uses bag semantics: a row appears in the output the minimum number of times it occurred across both inputs.
Copy code
-- table_a has 'x' three times, table_b has 'x' twice
SELECT val FROM table_a
INTERSECT ALL
SELECT val FROM table_b;
-- returns 'x' twice
Many mainstream databases only implement plain INTERSECT; DuckDB's support for the ALL variant follows the full ANSI SQL set-operation spec.
NULL handling
INTERSECT treats two NULLs as equal for the purpose of matching rows (unlike =, which treats NULL = NULL as unknown), so a NULL value present in both inputs is included in the intersection.
Related terms
EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference.
UNION and UNION ALL →UNION combines the result sets of two or more SELECT queries into one, removing duplicate rows, while UNION ALL does the same but keeps all duplicates.
NULL →NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.
JOIN clause →How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.
USING clause →The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
FAQS
INTERSECT compares whole rows across two independent queries for equality and returns only matching rows without adding new columns; a JOIN matches on specific key columns and can pull in additional columns from both sides.
Plain INTERSECT removes duplicates from its output; INTERSECT ALL (supported in DuckDB) keeps duplicates, returning each row the minimum number of times it appeared across both inputs.
No -- like UNION, columns are matched by position, not name, and the result's column names come from the first query.
