Join / semi anti · All operations

Keep unmatched rows from both tables

A variant of Keep rows found in another table

Keep the rows of either table whose key is missing from the other.

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • 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).

Tableau Prep (1)
  • notInnerread 2026-09-27

    Rows of both tables that have no match in the other

VisiData (4)
  • join-cols-diffread 2026-09-23

    Join on selected columns: keep rows whose key is missing from at least one sheet

  • join-selected-diffread 2026-09-23

    Join selected sheets: keep rows whose key is missing from at least one sheet

  • join-sheets-all-diffread 2026-09-23

    Join all stacked sheets: keep rows whose key is missing from at least one sheet

  • join-sheets-top2-diffread 2026-09-23

    Join top two sheets: keep rows whose key is missing from at least one sheet

Background

In Tableau Prep, a notInner join includes all of the values from the right and the left table that don't match.[1] In VisiData, a diff join keeps only the rows without a matching key on all of the sheets joined.[2]

When to use it

Keep the unmatched rows of both tables when reconciling two lists that should agree, such as the same customers held in two systems, so that the gaps on each side show at once. (Meridian's view) Where rows in a full outer join do not match, the result has NULL values for every column of the table that lacks a matching row.[3] Add a column naming the table each row came from, rather than reading the side from which columns are empty. (Meridian's view)

The method

A full outer join combines the effect of applying both left and right outer joins.[3] Some database systems that do not support the full outer join directly can emulate it with an inner join and UNION ALL selects of the rows found in only the left or the right table.[3]

Sources

  1. [1]Aggregate, Join, or Union Data - Tableaudocumentation
  2. [2]visidata/visidata/features/join.py at 7609212ec2febafa9caba7183cbebfaf49627909 · saulpw/visidata · GitHubdocumentation
  3. [3]Join (SQL) - Wikipediasecondary source