# Intersect or subtract tables

Join / setops

intersect, except

Page: <https://meridian.online/operations/intersect-or-subtract-tables>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## 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.

## Variants

- [Keep rows absent from the other table](https://meridian.online/operations/intersect-or-subtract-tables/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](https://meridian.online/operations/intersect-or-subtract-tables/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: [`EXCEPT`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`EXCEPT`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`INTERSECT`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`INTERSECT`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`Set Operations (UNION [ALL], INTERSECT [ALL], EXCEPT[ALL])`](https://datafusion.apache.org/user-guide/features.html) (read 2026-09-27)
- Apache DataFusion: [`except`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27)
- Apache DataFusion: [`except_distinct`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27)
- Apache DataFusion: [`intersect`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27)
- Apache DataFusion: [`intersect_distinct`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27)
- dbt-utils: [`except`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros) (read 2026-09-27)
- dbt-utils: [`intersect`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros) (read 2026-09-27)
- DuckDB: [`Set Operations (clause)`](https://duckdb.org/docs/current/sql/query_syntax/setops) (read 2026-09-27)
- Duckle: [`Except / Minus`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`Intersect`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Ibis: [`Difference`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/relations.py) (read 2026-09-27)
- Ibis: [`Intersection`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/relations.py) (read 2026-09-27)
- KNIME Analytics Platform: [`Set Operator`](https://api.hub.knime.com/nodes/*hI8TzZUNeTUVzkA5?details=full) (read 2026-09-27)
- Polars: [`SQL set operations (group)`](https://docs.pola.rs/api/python/stable/reference/sql/set_operations.html) (read 2026-09-27)