Join / semi anti · All operations

Keep rows without a match

A variant of Keep rows found in another table

Keep the rows whose key does not appear in another table.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the rows whose key is not in another table

SQL
CREATE TEMP TABLE l AS SELECT * FROM (VALUES
  (1, 'a', 'l1'), (2, 'a', 'l2'), (3, 'b', 'l3'), (4, NULL, 'l4'), (5, 'z', 'l5')) t(id, k, lv);
CREATE TEMP TABLE r AS SELECT * FROM (VALUES
  ('a', 'r1'), ('a', 'r2'), ('b', 'r3'), (NULL, 'r4'), ('y', 'r5')) t(k, rv);

CREATE TEMP TABLE no_match AS SELECT * FROM l ANTI JOIN r ON l.k = r.k;
CREATE TEMP TABLE has_match AS SELECT * FROM l SEMI JOIN r ON l.k = r.k;
-- NOT IN gives a different answer: one NULL key in r makes every comparison unknown, so it keeps nothing
CREATE TEMP TABLE not_in AS SELECT * FROM l WHERE k NOT IN (SELECT k FROM r);
SELECT * FROM no_match ORDER BY id;
SELECT CASE WHEN ((SELECT list(id ORDER BY id) = [4, 5] FROM no_match)
  AND (SELECT list(id ORDER BY id) = [1, 2, 3] FROM has_match)
  AND (SELECT count(*) = 0 FROM not_in)) THEN 'as expected' ELSE error('an anti join should keep the left rows 4 (NULL key) and 5 (no partner), a semi join the left rows 1, 2 and 3 once each however often their key repeats in r, and NOT IN should keep none') 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: “an anti join should keep the left rows 4 (NULL key) and 5 (no partner), a semi join the left rows 1, 2 and 3 once each however often their key repeats in r, and NOT IN should keep none”.
  • 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 (2)
Duckle (1)
  • Anti Joinread 2026-09-25

    keep rows whose key is absent from the other input

Power Query M (1)
RapidMiner Studio (1)
  • Set Minusread 2026-09-27

    Keep rows of the first table whose ID does not appear in the second table

Tableau Prep (2)
  • leftOnlyread 2026-09-27

    Rows of the left table with no match in the right table

  • rightOnlyread 2026-09-27

    Rows of the right table with no match in the left table

Background

An antijoin keeps only the rows of one table for which no row of another table is equal on their common columns.[1]

When to use it

In PostgreSQL, if no right-hand value equals the left-hand one and at least one right-hand row yields null, the result of NOT IN is null, not true.[2] Write a keep-without-a-match filter as an anti join or NOT EXISTS rather than NOT IN, unless the other table's key column is known never to hold a NULL. (Meridian's view) Decide whether rows whose own key is missing belong in the result before trusting a count of unmatched rows. (Meridian's view)

The method

In Apache DataFusion, NOT EXISTS returns all rows where a correlated subquery produces zero matches for that row.[3]

Compared with the alternatives

When two relations have the same attributes, the antijoin is the same as minus.[1] In DuckDB, the EXCEPT clause selects the rows that only occur in the left query, and plain EXCEPT removes duplicate rows from its result.[4] In DuckDB, traditional set operations such as EXCEPT combine queries by column position and require them to have the same number of columns.[4] Use an anti join when the tables share only a key, and EXCEPT when whole rows are being compared and a duplicate in the result is not wanted. (Meridian's view)

Sources

  1. [1]Join (relational algebra) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 9.24. Subquery Expressionsdocumentation
  3. [3]Subqueries — Apache DataFusion main documentationdocumentation
  4. [4]Set Operations – DuckDBdocumentation