Join / setops · All operations

Keep rows absent from the other table

A variant of Intersect or subtract tables

Keep the rows of the first input that do not appear in the second.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the rows of the first table that are absent from the second

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);

CREATE TEMP TABLE as_set AS SELECT * FROM a EXCEPT SELECT * FROM b;
CREATE TEMP TABLE with_duplicates AS SELECT * FROM a EXCEPT ALL SELECT * FROM b;
CREATE TEMP TABLE other_way AS SELECT * FROM b EXCEPT SELECT * FROM a;
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) = [(3, 'w')] FROM as_set)
  AND (SELECT list((k, v) ORDER BY k NULLS LAST) IS NOT DISTINCT FROM [(3, 'w'), (NULL, 'z')] FROM with_duplicates)
  AND (SELECT list((k, v) ORDER BY k) = [(4, 'u')] FROM other_way)) THEN 'as expected' ELSE error('a EXCEPT b should leave only (3, w); with duplicates it should also leave the second (NULL, z) that b does not cover; and b EXCEPT a should leave only (4, u)') 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: “a EXCEPT b should leave only (3, w); with duplicates it should also leave the second (NULL, z) that b does not cover; and b EXCEPT a should leave only (4, u)”.
  • 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)
  • 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

  • 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

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

    Emit the adapter-correct EXCEPT set operator

Duckle (1)
Ibis (1)
  • Differenceread 2026-09-27

    Keep the rows of the first table that are absent from the second

Background

In SQL, EXCEPT takes the distinct rows of one query and returns those that do not appear in a second result set.[1] In PostgreSQL, EXCEPT returns the rows in the result of the first query but not the second, sometimes called the difference between two queries.[2]

When to use it

Reach for EXCEPT to find what one table holds that another lacks, such as the customers who have never placed an order. (Meridian's view) Put the table whose leftover rows you want first, and run the query again with the inputs swapped to see the other side's leftovers. (Meridian's view)

In SQL, the EXCEPT operator does not distinguish between NULLs for purposes of row elimination and duplicate removal.[1] Oracle provides a MINUS operator that is functionally equivalent to the SQL standard EXCEPT DISTINCT operator.[1]

The method

In PostgreSQL, with EXCEPT ALL a row that has m duplicates in the left table and n duplicates in the right appears max(m - n, 0) times in the result.[3] In DuckDB, EXCEPT ALL of the values 5, 5, 6, 6, 6, 6, 7, 8 against 5, 6, 6, 7, 7, 9 returns 5, 8, 6 and 6.[4] Use EXCEPT ALL when a repeated row is a real second record, such as two identical sales, and the plain form when it is the same fact twice. (Meridian's view)

In PostgreSQL, multiple EXCEPT operators in the same SELECT statement are evaluated left to right unless parentheses dictate otherwise.[3]

Sources

  1. [1]Set operations (SQL) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 7.4. Combining Queries (UNION, INTERSECT, EXCEPT)documentation
  3. [3]PostgreSQL: Documentation: 18: SELECTdocumentation
  4. [4]Set Operations – DuckDBdocumentation