Join / setops · All operations

Intersect or subtract tables

Intersect, except.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

one table from two or more

Level

one clause of SQL

The DuckDB step

Intersect and except

It reads the orders and customers tables the proofs share. Create them first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL

CREATE TEMP TABLE result AS
SELECT 'both' AS side, customer FROM (SELECT customer FROM orders INTERSECT SELECT customer FROM customers)
UNION ALL SELECT 'customers only', customer FROM (SELECT customer FROM customers EXCEPT SELECT customer FROM orders);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT list_sort(list(customer) FILTER (WHERE side = 'both')) = ['Alice Ng', 'Bob Okafor', 'Carla Diaz'] AND list(customer) FILTER (WHERE side = 'customers only') = ['Frank Moore'] FROM result)) THEN 'as expected' ELSE error('the intersection or the difference is wrong') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the intersection or the difference is wrong”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Keep rows absent from the other table
Keep the rows of the first input that do not appear in the second.
Keep rows found in both tables
Keep the rows that appear in both inputs, as a set or with duplicates.

Combine queries by a set operation

Pick intersect, except or union as an option of one set-operation operator or clause.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache DataFusion (9)

EXCEPTread 2026-09-27
Pipe: subtract other queries, BigQuery-style |> syntax
EXCEPTread 2026-09-27
Rows of the left query absent from the right, ALL or DISTINCT
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
Set Operations (UNION [ALL], INTERSECT [ALL], EXCEPT[ALL])read 2026-09-27
Combine the rows of two queries as a set or bag
exceptread 2026-09-27
DataFrame method: Rows of this DataFrame absent from another
except_distinctread 2026-09-27
DataFrame method: Distinct rows of this DataFrame absent from another
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 (2)

exceptread 2026-09-27
Emit the adapter-correct EXCEPT set operator
intersectread 2026-09-27
Emit the adapter-correct INTERSECT set operator

DuckDB (1)

Set Operations (clause)read 2026-09-27
UNION, INTERSECT and EXCEPT, by position or by name

Duckle (2)

Except / Minusread 2026-09-25
rows in the first input only
Intersectread 2026-09-25
rows present in all inputs

Ibis (2)

Differenceread 2026-09-27
Keep the rows of the first table that are absent from the second
Intersectionread 2026-09-27
Keep the rows two tables have in common

KNIME Analytics Platform (1)

Set Operatorread 2026-09-27
Intersection, union, complement or exclusive-or of the value sets of two columns

Polars (1)

SQL set operations (group)read 2026-09-27
7 operations: EXCEPT, INTERSECT, UNION, UNION ALL and others; each with a BY NAME form that aligns columns by name

Background

In SQL, INTERSECT returns only the rows that appear in both of two result sets, and EXCEPT returns the rows of one that do not appear in the other.^[1] SQL's set operators UNION, INTERSECT and EXCEPT correspond mathematically to union, intersection and set difference.^[1]

When to use it

In PostgreSQL, UNION, INTERSECT and EXCEPT eliminate duplicate rows unless ALL is specified, even though ALL is the default for SELECT itself.^[2] Decide whether a repeated row is one fact or several before choosing between the plain form and the ALL form. (Meridian's view)

In DuckDB, traditional set operations match columns by position, and the result uses the column names from the first query.^[3] Name the same columns in the same order on both sides, and read the column list rather than trusting SELECT * on two tables that only look alike. (Meridian's view)

In PostgreSQL, INTERSECT binds more tightly than UNION and EXCEPT, which associate left to right when there are no parentheses.^[4] In PostgreSQL, a clause such as LIMIT after the last input is read as applying to the output of the set operation unless that input is in parentheses.^[4] When a query chains several set operations, write the parentheses even where the default order would give the same answer. (Meridian's view)

The method

Relational algebra was introduced by Edgar F. Codd as part of his relational model of data in 1970.^[5] Relational algebra uses set union, set difference and Cartesian product from set theory.^[5] In relational algebra, the two relations in a set union or set difference must be union-compatible, having the same set of attributes.^[5] Set intersection in relational algebra is defined in terms of set union and set difference, so its two relations must also be union-compatible.^[5]

In DuckDB, the plain set operations use set semantics and eliminate duplicates, while the variants with ALL use bag semantics.^[3]

Compared with the alternatives

In DataFusion, set operations operate on whole rows rather than on individual columns, and the input queries must produce compatible columns.^[6] In DuckDB, semi and anti joins return the rows of the left table that have, or have no, match in the right table, and never return more rows than the left table.^[7]

Sources

[1]Set operations (SQL) - Wikipediasecondary source
[2]PostgreSQL: Documentation: 18: SELECTdocumentation
[3]Set Operations – DuckDBdocumentation
[4]PostgreSQL: Documentation: 18: 7.4. Combining Queries (UNION, INTERSECT, EXCEPT)documentation
[5]Relational algebra - Wikipediasecondary source
[6]SELECT syntax — Apache DataFusion main documentationdocumentation
[7]FROM and JOIN Clauses – DuckDBdocumentation