# Keep unmatched rows from both tables

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 of either table whose key is missing from the other.

Page: <https://meridian.online/operations/keep-rows-found-in-another-table/keep-unmatched-rows-from-both-tables>

## Status

Runs as a SQL step.

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

## The DuckDB step

keep the unmatched rows of both tables

```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 result AS
SELECT 'left' AS side, l.lv AS row_name, l.k FROM l ANTI JOIN r ON l.k = r.k
UNION ALL
SELECT 'right', r.rv, r.k FROM r ANTI JOIN l ON l.k = r.k;
SELECT * FROM result ORDER BY side, row_name;
SELECT CASE WHEN ((SELECT list((side, row_name) ORDER BY side, row_name) = [('left', 'l4'), ('left', 'l5'), ('right', 'r4'), ('right', 'r5')] FROM result)
  AND (SELECT count(*) FROM l FULL JOIN r ON l.k = r.k WHERE l.lv IS NULL OR r.rv IS NULL) = 4) THEN 'as expected' ELSE error('the result should hold the left rows l4 and l5 and the right rows r4 and r5, the same 4 rows a full join leaves without a partner, with both NULL-key rows unmatched') 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).

- Tableau Prep: [`notInner`](https://help.tableau.com/current/prep/en-us/prep_combine.htm) (read 2026-09-27)
- VisiData: [`join-cols-diff`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L423) (read 2026-09-23)
- VisiData: [`join-selected-diff`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L420) (read 2026-09-23)
- VisiData: [`join-sheets-all-diff`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L422) (read 2026-09-23)
- VisiData: [`join-sheets-top2-diff`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L421) (read 2026-09-23)