Shape / rows · All operations

Keep rows with chosen values

A variant of Filter rows

Keep or exclude the rows whose value is one of a picked list of values.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep or exclude rows whose value is one of a picked list

It reads the orders table the proofs share. Create it 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);

Then run the statement:

SQL
CREATE TEMP TABLE picked AS SELECT * FROM (VALUES ('north'), ('west')) t(region);
-- keep the picked values, from a literal list and from a table of picks
CREATE TEMP TABLE kept AS SELECT order_id FROM orders WHERE region IN ('north', 'west');
CREATE TEMP TABLE kept_from_table AS SELECT order_id FROM orders WHERE region IN (SELECT region FROM picked);
-- exclude them; NOT IN alone would drop the row whose region is missing, so keep it explicitly
CREATE TEMP TABLE excluded AS SELECT order_id FROM orders WHERE region IS NULL OR region NOT IN ('north', 'west');
SELECT list(order_id ORDER BY order_id) AS kept FROM kept;
SELECT list(order_id ORDER BY order_id) AS excluded FROM excluded;
SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) FROM kept) = [1, 2, 6, 8, 10] AND (SELECT list(order_id ORDER BY order_id) FROM kept_from_table) = [1, 2, 6, 8, 10] AND (SELECT list(order_id ORDER BY order_id) FROM excluded) = [3, 4, 5, 7, 9]) THEN 'as expected' ELSE error('the picked regions were not orders 1, 2, 6, 8 and 10, or the others, with the order that has no region, were not 3, 4, 5, 7 and 9') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the picked regions were not orders 1, 2, 6, 8 and 10, or the others, with the order that has no region, were not 3, 4, 5, 7 and 9”.
  • 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).

EasyMorph (1)
  • Filterread 2026-09-27

    Keep or remove rows by ticking the values of a column

KNIME Analytics Platform (1)
Tableau Prep (3)
  • Excluderead 2026-09-27

    One-click filter dropping the rows with the selected values

  • Keep Onlyread 2026-09-27

    One-click filter keeping the rows with the selected values

  • Selected Valuesread 2026-09-27

    Keep or exclude a picked list of values, including values absent from the sample

Trifacta (1)
  • Filter inread 2026-09-27

    Keep or delete rows whose value is one of a list of values

Background

In PostgreSQL, IN with a list of values is true if the expression equals any of them, a shorthand for equality tests joined by OR.[1] In PostgreSQL, NOT IN with a list is true if the expression is unequal to all of the values, a shorthand for inequality tests joined by AND.[1]

When to use it

In PostgreSQL, if the tested value is null, or no listed value is equal and at least one is null, IN gives null rather than false and NOT IN gives null rather than true.[1] In PostgreSQL, x NOT IN y is equivalent to NOT (x IN y) in all cases, yet null values are much more likely to trip up the novice with NOT IN than with IN.[1] When excluding a picked list, decide what happens to rows whose value is missing and say so with an explicit IS NULL test, and make sure the list itself holds no nulls. (Meridian's view)

Keep a long or changing list of chosen values in a table and filter with a subquery, so the picks are data that can be reviewed rather than literals inside a query. (Meridian's view)

The method

In DuckDB, IN with a parenthesised tuple returns true if the value is present, false if it is absent and the tuple has no NULL, and NULL if it is absent and the tuple has a NULL.[2] In DuckDB, IN with a square-bracketed list follows the semantics used in Python, and a NULL in the list makes no difference to the result.[2] In DuckDB, a subquery on the right of IN must return a single column, and one returning more raises a Binder Error.[2] In PostgreSQL, the subquery form of IN compares the expression with each row of the subquery result, and is false if no equal row is found, including when the subquery returns no rows.[3]

Compared with the alternatives

In PostgreSQL, EXISTS is true if its subquery returns at least one row and false if it returns none.[3] To exclude rows listed in another table, a NOT EXISTS test is the safer form when the list's column can hold nulls. (Meridian's view)

Sources

  1. [1]PostgreSQL: Documentation: 18: 9.25. Row and Array Comparisonsdocumentation
  2. [2]IN Operator – DuckDBdocumentation
  3. [3]PostgreSQL: Documentation: 18: 9.24. Subquery Expressionsdocumentation