Shape / rows · All operations

Filter rows

Keep or drop rows by a condition.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
fewer rows, same columns
Level
one clause of SQL

The DuckDB step

Keep rows by a condition

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 result AS
SELECT * FROM orders WHERE amount > 100 AND region <> 'west';
SELECT * FROM result;
SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) = [1, 3, 10] FROM result)) THEN 'as expected' ELSE error('the filter did not keep orders 1, 3 and 10') 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 filter did not keep orders 1, 3 and 10”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Keep rows that match a condition

Keep, or drop, the rows for which a condition on their values is true.

Reuse a named filter

Define a filter condition once, by name or macro, and apply it in several queries.

Find the positions of matching rows

Return the row numbers at which a condition is true rather than the rows themselves.

Keep the selected rows

Keep or remove the rows a person has selected, highlighted or flagged in an interactive view.

Split rows into separate outputs

Send each row to one of two or more outputs by the condition it meets, so no row is discarded.

Keep rows past a watermark

Pass only the rows newer than the last successful run.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • Filterread 2026-09-27

    Route rows to a True or a False output by a condition

  • Filter In-DBread 2026-09-27

    Filter records inside the database with a basic filter or a native SQL expression

Apache DataFusion (5)
  • Filter (WHERE)read 2026-09-27

    Keep rows matching a predicate before grouping

  • Filter post-aggregate (HAVING)read 2026-09-27

    Keep groups matching a predicate after aggregation

  • QUALIFY clauseread 2026-09-27

    Filter rows after window functions are evaluated

  • WHEREread 2026-09-27

    Pipe: filter rows, BigQuery-style |> syntax

  • filterread 2026-09-27

    DataFrame method: Keep rows for which a predicate is true

Apache Hop (3)
  • Filter Rowsread 2026-09-27

    Route rows to a true or false target by a condition tree

  • Java Filterread 2026-09-27

    Route rows to a true or false target by a Java boolean expression

  • Switch / Caseread 2026-09-27

    Route each row to a target chosen by the value of a field, with a default target

Apache NiFi (3)
  • Filter Functionsread 2026-09-27

    8 functions: contains, containsRegex, matchesRegex, startsWith, endsWith, …

  • RouteTextread 2026-09-27

    Route each line of text by user-defined rules, one relationship per rule

  • ScriptedFilterRecordread 2026-09-27

    Keep records for which a user script returns true

Apache SeaTunnel (1)
  • FilterRowKindread 2026-09-27

    Keep or drop rows by change kind: insert, update-before, update-after, delete

AWS Glue (5)
  • Conditional Routerread 2026-09-27

    Evaluate each row against several group filter conditions and send it to its group's output

  • Filterread 2026-09-27

    Keep records that satisfy a predicate function

  • Filterread 2026-09-27

    Split a dataset in two by a filter condition

  • Remove null rowsread 2026-09-27

    Remove rows in which every column is null or empty

  • SplitRowsread 2026-09-27

    Split a frame into two by row: the rows that match a comparison and the remaining rows

Azure Data Factory (4)
  • Conditional splitread 2026-09-27

    Route rows to different output streams by matching conditions

  • Filterread 2026-09-27

    Apply a filter expression to an input array inside the pipeline

  • Filterread 2026-09-27

    Keep rows that meet a condition

  • Row Filteringread 2026-09-27

    Table.SelectRows with equality, comparison, text containment and date range conditions

Bruin (2)
  • Filtersread 2026-09-27

    Structured filters with 11 operators: equals, not_equals, gt, in, between, is_null, …

  • Segmentsread 2026-09-27

    Named reusable filters applied to semantic queries

DuckDB (3)
Duckle (2)
  • Filter Rowsread 2026-09-25

    keep rows where a predicate holds; optional reject port

  • Incremental Loadread 2026-09-25

    pass rows past the last successful run's watermark; mark kept in workspace state

EasyMorph (5)
  • Filterread 2026-09-27

    Keep or remove rows by ticking the values of a column

  • Filter by conditionread 2026-09-27

    Keep rows for which an expression evaluates to true

  • Filter by searchread 2026-09-27

    Keep or remove rows that contain a search string

  • Filter by typeread 2026-09-27

    Keep or remove rows by the data type of their values, such as errors

  • Remove empty rowsread 2026-09-27

    Drop rows whose cells are all empty or whitespace

Ibis (2)
  • DropNullread 2026-09-27

    Drop rows that hold nulls in any or all of a subset of columns

  • Filterread 2026-09-27

    Keep the rows that satisfy every predicate in a list

KNIME Analytics Platform (18)
OpenRefine (5)
  • Facet by flagread 2026-09-27

    Split rows true or false by whether the row is flagged

  • Facet by starread 2026-09-27

    Split rows true or false by whether the row is starred

  • Keep only matching rowsread 2026-09-27

    Delete every row outside the current facet selection

  • Remove matching rowsread 2026-09-27

    Delete the rows in the current facet selection

  • Text filterread 2026-09-27

    Keep rows whose cell contains a typed string or matches a regular expression; can be inverted

Pentaho Data Integration (3)
  • Filter Rowsread 2026-09-27

    Filter rows with simple comparison conditions

  • Java Filterread 2026-09-27

    Filter rows with a Java expression

  • Switch / Caseread 2026-09-27

    Route each row to a target step chosen by the value of a field

Polars (7)
Power Query M (4)
RapidMiner Studio (4)
SQLMesh (4)
  • @ANDread 2026-09-27

    Join operands with AND, dropping NULL expressions

  • @HAVINGread 2026-09-27

    Include a HAVING clause only when the argument is true

  • @ORread 2026-09-27

    Join operands with OR, dropping NULL expressions

  • @WHEREread 2026-09-27

    Include a filtering WHERE clause only when the argument is true

Tableau Prep (10)
  • Calculationread 2026-09-27

    Filter rows by a boolean calculation; the one filter type offered in the Input step

  • Excluderead 2026-09-27

    One-click filter dropping the rows with the selected values

  • Filterread 2026-09-27

    Keep or exclude rows by a filter on a field

  • Keep Onlyread 2026-09-27

    One-click filter keeping the rows with the selected values

  • Null Valuesread 2026-09-27

    Show the null values of a field or exclude them

  • Range of Datesread 2026-09-27

    Filter a date field by a range, a minimum or a maximum date

  • Range of Valuesread 2026-09-27

    Filter a number field by a range, a minimum or a maximum

  • Relative Dateread 2026-09-27

    Filter a date field to a span of years, quarters, months, weeks or days relative to an anchor

  • Selected Valuesread 2026-09-27

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

  • Wildcard Matchread 2026-09-27

    Keep or exclude string values matching a pattern

Talend (3)
  • tCombinedSQLFilterread 2026-09-27

    Filter rows and remap columns of a source table inside the DBMS

  • tFilterRowread 2026-09-27

    Keep rows meeting one or more conditions on chosen columns

  • tSQLTemplateFilterRowsread 2026-09-27

    Filter the rows of a table with a WHERE clause, run in the DBMS

Trifacta (14)
  • Delete Transformread 2026-09-27

    Delete the rows for which a condition is true

  • Filter Transformread 2026-09-27

    Keep or delete rows by a defined type of filter

  • Filter containsread 2026-09-27

    Keep or delete rows whose value contains a literal or pattern

  • Filter custom formularead 2026-09-27

    Keep or delete rows by an arbitrary formula

  • Filter ends withread 2026-09-27

    Keep or delete rows whose value ends with a literal or pattern

  • Filter exactread 2026-09-27

    Keep or delete rows whose value equals a given value

  • Filter greater thanread 2026-09-27

    Keep or delete rows with values greater than, or equal to, a given value

  • Filter inread 2026-09-27

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

  • Filter less thanread 2026-09-27

    Keep or delete rows with values less than, or equal to, a given value

  • Filter mismatchedread 2026-09-27

    Keep or delete rows whose values mismatch the column's data type

  • Filter missingread 2026-09-27

    Keep or delete rows with missing values

  • Filter not equalsread 2026-09-27

    Keep or delete rows whose value does not equal a given value

  • Filter starts withread 2026-09-27

    Keep or delete rows whose value starts with a literal or pattern

  • Keep Transformread 2026-09-27

    Keep the rows for which a condition is true and remove the rest

VisiData (13)
  • delete-cursorread 2026-09-23

    Delete source rows inside the canvas cursor box

  • delete-selectedread 2026-09-23

    Delete all selected rows

  • delete-visibleread 2026-09-23

    Delete source rows visible on the canvas

  • dive-cursorread 2026-09-23

    Open copy of source sheet holding the rows inside the canvas cursor box

  • dive-exceptread 2026-09-23

    Open copy of source sheet excluding the rows in current bin

  • dive-except-selectedread 2026-09-23

    Open copy of source sheet excluding the rows in selected bins

  • dive-selectedread 2026-09-23

    Open the selected rows; on a frequency table, one sheet of the selected bins' source rows

  • dive-visibleread 2026-09-23

    Open copy of source sheet holding the rows visible on the canvas

  • dup-searchread 2026-09-23

    (experimental) Open duplicate sheet filtered live to rows matching a regex in current column

  • dup-search-colsread 2026-09-23

    (experimental) Open duplicate sheet filtered live to rows matching a regex in any column

  • dup-selectedread 2026-09-23

    Open duplicate sheet holding references to the selected rows

  • dup-selected-deepread 2026-09-23

    Open duplicate sheet holding deep copies of the selected rows

  • open-selectedread 2026-09-23

    (vdsql) Open the rows of the selected frequency bins

Background

In SQL, the WHERE clause filters rows: it eliminates every row of the result for which its predicate does not evaluate to true.[1] The WHERE clause is approximately the relational algebra selection operation, a unary operation that denotes a subset of a relation.[1][2] A WHERE filter is also known as a horizontal partition, restricting the rows a query outputs to those meeting specified conditions.[1]

When to use it

In PostgreSQL, each row is checked against the WHERE condition and kept only if the result is true; a false or null result discards the row.[3] In SQL, a comparison with a null can never result in either true or false, but always in a third logical result, Unknown.[4] In SQL, rows for which the WHERE predicate evaluates to either False or Unknown are discarded by SELECT queries.[4] Interpreting Unknown and False as the same logical result is a common error when dealing with nulls in SQL.[4]

In SQL's three-valued logic, p OR NOT p is unknown whenever p is unknown, so WHERE (x = 10) OR NOT (x = 10) does not return the rows in which x is null.[4] On a column that can be empty, decide whether the empty rows should be kept or dropped, and write IS NULL or IS NOT NULL into the condition to say so. (Meridian's view) Filter as early as the question allows, so that later joins and aggregates work on fewer rows. (Meridian's view)

The method

Relational algebra was introduced by Edgar F. Codd as part of his relational model of data in 1970.[2] In relational algebra, a selection is sometimes called a restriction, in reference to E.F. Codd's 1970 paper, which predates the existence of SQL.[2] A selection is written σ with its condition and the relation, as in σ Age ≥ 34 (Person), and denotes all tuples in the relation for which the condition holds.[2] Selection is idempotent, so applying the same selection twice has no further effect, and commutative, so the order selections are applied in does not change the result.[2]

In PostgreSQL, the WHERE search condition is any value expression that returns a value of type boolean, and it typically references at least one column of the table generated in the FROM clause.[3]

In query optimisation, moving selections towards the leaves of an expression tree is likely to shrink the intermediate relations, because selection very effectively decreases the number of rows in its operand.[2] Selection is distributive over set difference, intersection and union, and rules based on that property are used to push a selection below set operations in an expression tree.[2]

Compared with the alternatives

In SQL, the SELECT list is approximately the relational algebra projection operation, which restricts output to specified columns, while WHERE performs a selection, which restricts rows.[1] In PostgreSQL, the join condition of an inner join can be written either in the WHERE clause or in the JOIN clause, and the two forms are equivalent.[3] In PostgreSQL, the ON or USING clause of an outer join is not equivalent to a WHERE condition, because it results in the addition of rows for unmatched input rows as well as the removal of rows.[3] With an outer join, decide whether a condition on the optional table should limit what is matched or which rows survive, and place it in ON or in WHERE accordingly. (Meridian's view) In SQL, rows for which a WHERE predicate evaluates to False or Unknown are not acted on by INSERT, UPDATE or DELETE statements.[4]

Sources

  1. [1]Select (SQL) - Wikipediasecondary source
  2. [2]Relational algebra - Wikipediasecondary source
  3. [3]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  4. [4]Null (SQL) - Wikipediasecondary source

Used in

8 Protocols use this operation: