# Keep rows without a match

Join / semi anti

A variant of [Keep rows found in another table](https://meridian.online/operations/keep-rows-found-in-another-table).

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

Page: <https://meridian.online/operations/keep-rows-found-in-another-table/keep-rows-without-a-match>

## Status

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.

## What other tools call it

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

- Apache DataFusion: [`LEFT ANTI JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`RIGHT ANTI JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Duckle: [`Anti Join`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Power Query M: [`Table.RemoveMatchingRows`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#membership) (read 2026-09-27)
- RapidMiner Studio: [`Set Minus`](https://docs.rapidminer.com/latest/studio/operators/blending/table/joins/set_minus.html) (read 2026-09-27)
- Tableau Prep: [`leftOnly`](https://help.tableau.com/current/prep/en-us/prep_combine.htm) (read 2026-09-27)
- Tableau Prep: [`rightOnly`](https://help.tableau.com/current/prep/en-us/prep_combine.htm) (read 2026-09-27)