Join / setops · All operations

Keep rows found in both tables

A variant of Intersect or subtract tables

Keep the rows that appear in both inputs, as a set or with duplicates.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the rows found in both tables, as a set and with duplicates

SQL
CREATE TEMP TABLE a AS SELECT * FROM (VALUES
  (1, 'x'), (1, 'x'), (2, 'y'), (NULL, 'z'), (NULL, 'z'), (3, 'w')) t(k, v);
CREATE TEMP TABLE b AS SELECT * FROM (VALUES
  (1, 'x'), (1, 'x'), (1, 'x'), (NULL, 'z'), (2, 'y'), (4, 'u')) t(k, v);

-- a holds (1, x) twice and (NULL, z) twice; b holds (1, x) three times and (NULL, z) once; NULLs count as equal
CREATE TEMP TABLE as_set AS SELECT * FROM a INTERSECT SELECT * FROM b;
CREATE TEMP TABLE with_duplicates AS SELECT * FROM a INTERSECT ALL SELECT * FROM b;
SELECT * FROM as_set ORDER BY k NULLS LAST;
SELECT * FROM with_duplicates ORDER BY k NULLS LAST;
SELECT CASE WHEN ((SELECT list((k, v) ORDER BY k NULLS LAST) IS NOT DISTINCT FROM [(1, 'x'), (2, 'y'), (NULL, 'z')] FROM as_set)
  AND (SELECT list((k, v) ORDER BY k NULLS LAST) IS NOT DISTINCT FROM [(1, 'x'), (1, 'x'), (2, 'y'), (NULL, 'z')] FROM with_duplicates)) THEN 'as expected' ELSE error('the intersection should be 3 distinct rows as a set, and 4 rows with duplicates, where (1, x) stays twice (the smaller count) and (NULL, z) once') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the intersection should be 3 distinct rows as a set, and 4 rows with duplicates, where (1, x) stays twice (the smaller count) and (NULL, z) once”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (4)
  • INTERSECTread 2026-09-27

    Pipe: intersect with other queries, BigQuery-style |> syntax

  • INTERSECTread 2026-09-27

    Rows that appear in both queries, ALL or DISTINCT

  • intersectread 2026-09-27

    DataFrame method: Intersection of two DataFrames with the same schema

  • intersect_distinctread 2026-09-27

    DataFrame method: Distinct intersection of two DataFrames

dbt-utils (1)
  • intersectread 2026-09-27

    Emit the adapter-correct INTERSECT set operator

Duckle (1)
  • Intersectread 2026-09-25

    rows present in all inputs

Ibis (1)
  • Intersectionread 2026-09-27

    Keep the rows two tables have in common

Background

In SQL, INTERSECT takes the results of two queries and returns only the rows that appear in both result sets.[1] In PostgreSQL, INTERSECT computes the set intersection of the rows returned by the SELECT statements involved.[2]

When to use it

Reach for INTERSECT to find the rows two sources agree on, such as the customers present in both a sales export and a billing table. (Meridian's view) For purposes of duplicate removal, the SQL INTERSECT operator does not distinguish between NULLs.[1]

The method

In PostgreSQL, with INTERSECT ALL a row that has m duplicates in the left table and n duplicates in the right appears min(m, n) times in the result.[2] In DuckDB, INTERSECT ALL of the values 5, 5, 6, 6, 6, 6, 7, 8 and 5, 6, 6, 7, 7, 9 returns 5, 6, 6 and 7.[3] Use INTERSECT ALL when each copy of a row is a separate record and the result should say how many copies both sides share. (Meridian's view)

Compared with the alternatives

In DuckDB, semi joins provide the same logic as the IN operator.[4] Pick INTERSECT when rows must agree in every selected column, and a semi join when a matching key is enough and the rest of the left row should come along. (Meridian's view)

Sources

  1. [1]Set operations (SQL) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: SELECTdocumentation
  3. [3]Set Operations – DuckDBdocumentation
  4. [4]FROM and JOIN Clauses – DuckDBdocumentation