# Keep rows found in another table Join / semi anti keep or drop rows by existence in another table Page: ## Status Runs as a SQL step. arcform carries it as a `sql:` step today. ## The DuckDB step customers with no orders It reads the `orders` and `customers` tables the proofs share. Create them first: ```sql CREATE TABLE orders AS SELECT * FROM (VALUES (1, 'Alice Ng', 'north', DATE '2026-01-03', 120.00, 'widget', 'a|b', '{"channel":"web","items":[1,2]}'), (2, 'alice ng', 'north', DATE '2026-01-05', 80.50, 'gadget', 'b', '{"channel":"shop","items":[3]}'), (3, 'Bob Okafor', 'south', DATE '2026-01-05', 300.00, 'widget', 'a|c', '{"channel":"web","items":[4,5,6]}'), (4, 'Carla Díaz', 'east', DATE '2026-01-09', NULL, 'gizmo', NULL, '{"channel":"web","items":[]}'), (5, 'Bob Okafor', 'south', DATE '2026-01-12', 45.25, 'gadget', 'c', '{"channel":"phone","items":[7]}'), (6, 'Dev Patel', 'west', DATE '2026-01-12', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (7, 'Carla Diaz', 'east', DATE '2026-01-20', 99.99, 'gizmo', 'b|c', '{"channel":"shop","items":[10]}'), (8, 'Dev Patel', 'west', DATE '2026-02-02', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (9, 'Eve Larsen', NULL, DATE '2026-02-03', 15.00, 'gadget', NULL, '{"channel":"web","items":[11]}'), (10, 'Alice Ng', 'north', DATE '2026-02-10', 220.00, 'gizmo', 'a|b|c', '{"channel":"phone","items":[12,13]}') ) t(order_id, customer, region, order_date, amount, product, tags, meta); CREATE TABLE customers AS SELECT * FROM (VALUES ('Alice Ng', 'retail', DATE '2025-06-01', 52.52, 13.40), ('Bob Okafor', 'wholesale', DATE '2025-09-15', 48.85, 2.35), ('Carla Diaz', 'retail', DATE '2025-11-30', 40.42, -3.70), ('Frank Moore', 'retail', DATE '2026-01-01', 51.51, -0.13) ) t(customer, segment, joined, lat, lon); ``` Then run the statement: ```sql CREATE TEMP TABLE result AS SELECT * FROM customers ANTI JOIN orders USING (customer); SELECT * FROM result; SELECT CASE WHEN ((SELECT list(customer) = ['Frank Moore'] FROM result) AND (SELECT count(*) = 3 FROM customers SEMI JOIN orders USING (customer))) THEN 'as expected' ELSE error('the customers without and with orders are not Frank Moore and the other three') END AS verdict; ``` It needs nothing installed beyond DuckDB itself. ## Variants - [Keep rows without a match](https://meridian.online/operations/keep-rows-found-in-another-table/keep-rows-without-a-match): Keep the rows whose key does not appear in another table. - Keep rows with a match: Keep the rows whose key appears in another table, with their own columns only. - Split rows by whether they match: Send matching rows to one output and the rest to another. - [Keep unmatched rows from both tables](https://meridian.online/operations/keep-rows-found-in-another-table/keep-unmatched-rows-from-both-tables): Keep the rows of either table whose key is missing from the other. ## What other tools call it Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2). - Azure Data Factory: [`Exists`](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-exists) (read 2026-09-27) - Apache DataFusion: [`LEFT ANTI JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27) - Apache DataFusion: [`LEFT SEMI 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) - Apache DataFusion: [`RIGHT SEMI JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27) - Apache DataFusion: [`[ NOT ] EXISTS`](https://datafusion.apache.org/user-guide/sql/subqueries.html) (read 2026-09-27) - Apache DataFusion: [`[ NOT ] IN`](https://datafusion.apache.org/user-guide/sql/subqueries.html) (read 2026-09-27) - Duckle: [`Anti Join`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`Semi Join`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - EasyMorph: [`Keep/remove matching`](https://help.easymorph.com/doku.php?id=transformations:keepremovematching) (read 2026-09-27) - Ibis: [`ExistsSubquery`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/subqueries.py) (read 2026-09-27) - Ibis: [`InSubquery`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/subqueries.py) (read 2026-09-27) - KNIME Analytics Platform: [`Reference Row Filter`](https://api.hub.knime.com/nodes/*LKxrCJihG46anH0Q?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Reference Row Splitter`](https://api.hub.knime.com/nodes/*LaMrKSen-Or6SfMS?details=full) (read 2026-09-27) - Apache NiFi: [`ScanAttribute`](https://nifi.apache.org/components/org.apache.nifi.processors.standard.ScanAttribute/) (read 2026-09-27) - Apache NiFi: [`ScanContent`](https://nifi.apache.org/components/org.apache.nifi.processors.standard.ScanContent/) (read 2026-09-27) - Power Query M: [`Table.RemoveMatchingRows`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#membership) (read 2026-09-27) - RapidMiner Studio: [`Intersect`](https://docs.rapidminer.com/latest/studio/operators/blending/table/joins/intersect.html) (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: [`notInner`](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) - 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)