Shape / columns · All operations

Compare and combine conditions

Comparison and boolean expressions.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Comparison and boolean expressions

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 order_id, amount BETWEEN 50 AND 250 AS mid, region IN ('north', 'south') AND NOT (product = 'gizmo') AS core_line,
       region IS DISTINCT FROM 'west' AS not_west FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE mid) = 4 AND count(*) FILTER (WHERE core_line) = 4 AND count(*) FILTER (WHERE not_west) = 8 AND count(*) FILTER (WHERE mid IS NULL) = 1 FROM result)) THEN 'as expected' ELSE error('a comparison counted the wrong rows, or the null amount did not stay null') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a comparison counted the wrong rows, or the null amount did not stay null”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Ibis (13)
  • Andread 2026-09-27

    Logical AND of two boolean values

  • Betweenread 2026-09-27

    Test whether a value lies within a lower and an upper bound

  • Equalsread 2026-09-27

    Test whether two values are equal

  • Greaterread 2026-09-27

    Test whether the left value is greater than the right

  • GreaterEqualread 2026-09-27

    Test whether the left value is greater than or equal to the right

  • IdenticalToread 2026-09-27

    Null-safe equality: two nulls compare as equal

  • InValuesread 2026-09-27

    Test whether a value is one of a given list of values

  • Lessread 2026-09-27

    Test whether the left value is less than the right

  • LessEqualread 2026-09-27

    Test whether the left value is less than or equal to the right

  • Notread 2026-09-27

    Negate a boolean value

  • NotEqualsread 2026-09-27

    Test whether two values differ

  • Orread 2026-09-27

    Logical OR of two boolean values

  • Xorread 2026-09-27

    Logical exclusive OR of two boolean values

OpenRefine (1)
Polars (5)

Background

A Boolean expression is an expression that produces a true or false value when evaluated, such as the comparison 5 > 3, which is true.[1] Most programming languages have the Boolean operators OR, AND and NOT.[1] In SQL, a comparison with NULL can never result in true or false, but always in a third logical result, unknown.[2]

When to use it

Reach for a comparison whenever a row needs a yes-or-no answer: to filter rows, to flag them in a new column, or to decide which branch of a CASE expression applies. (Meridian's view) In DuckDB, Boolean values are most often created by comparisons or conjunctions, and can filter rows in the WHERE and HAVING clauses.[3]

Interpreting an unknown result as false is a common error when working with NULLs in SQL.[2] In SQL, a filter written as i = NULL logically returns no rows, because a comparison with NULL is unknown even where i is NULL.[2] PostgreSQL advises against writing expression = NULL, because NULL is not equal to NULL, and offers IS NULL and IS NOT NULL instead.[4] Test for a missing value with IS NULL or IS NOT NULL, never with an equals sign. (Meridian's view) In SQL's three-valued logic, a filter such as x = 10 OR NOT (x = 10) does not return the rows in which x is NULL.[2] When a condition and its opposite should between them cover every row, add an explicit test for NULL to one of them. (Meridian's view)

In DuckDB, IN on a tuple returns NULL rather than false when the value is not in the tuple and the tuple holds a NULL.[5] In DuckDB, x NOT IN y is equivalent to NOT (x IN y).[5] In DuckDB, NOT applied to NULL gives NULL.[6] In SQL, WHERE 1 NOT IN a subquery that returns only NULL keeps no row, because IN is an iterated equality and 1 <> NULL is unknown.[2] In DuckDB, IN on a list follows Python's semantics, so a NULL in the list does not change the result.[5] Before writing NOT IN against a column that may hold NULLs, decide what a missing value should mean, and filter the NULLs out first if they should not count. (Meridian's view)

In DuckDB, a BETWEEN x AND y is equivalent to x <= a AND a <= y when a, x and y are of the same type, because BETWEEN casts all its inputs to one type.[7] In DuckDB, BETWEEN x AND y with x greater than y is always false.[7] In PostgreSQL, BETWEEN includes both endpoints, and BETWEEN SYMMETRIC swaps the two endpoints when the first is the larger.[4] Write the lower bound of a BETWEEN first, and where a range should stop just short of its upper end, write the two comparisons out instead. (Meridian's view) In PostgreSQL, an expression such as 1 < 2 < 3 is not valid, because each comparison returns a Boolean, and BETWEEN is the way to test a range.[4]

The method

Boolean algebra was introduced by George Boole in The Mathematical Analysis of Logic (1847) and set forth more fully in An Investigation of the Laws of Thought (1854).[8] In SQL, NULL is a marker for missing information, introduced by E. F. Codd, the creator of the relational database model.[2] SQL implements a three-valued logic (3VL), because a comparison with NULL can be neither true nor false and gives a third result, unknown.[2] The truth tables SQL uses for AND, OR and NOT correspond to a common fragment of the Kleene and Łukasiewicz three-valued logics.[2][9] Jan Łukasiewicz initially developed three-valued logic to represent the truth value of statements about the undetermined future.[9]

In Kleene's logic, true OR unknown is true, because true OR true and true OR false are both true, whichever value the unknown stands for.[9] In DuckDB, NULL AND false evaluates to false and NULL OR true evaluates to true.[6] In DuckDB and PostgreSQL, AND and OR are commutative, so swapping the left and right operands does not change the result.[6][10] PostgreSQL does not guarantee that the left operand of AND or OR is evaluated before the right.[10] Do not count on the left-hand condition to shield the right-hand one from an error. (Meridian's view)

SQL defines any two values that are equal, or any two NULLs, as not distinct, and that definition lets GROUP BY group NULLs together.[2] The SQL standard's optional feature F571, Truth value tests, adds the predicates IS TRUE, IS FALSE and IS UNKNOWN, and was present in SQL92.[2] In PostgreSQL, IS TRUE, IS FALSE, IS UNKNOWN and their negations always return true or false, never NULL.[4]

Compared with the alternatives

In PostgreSQL, IS DISTINCT FROM is the same as <> for non-NULL inputs, but returns false when both inputs are NULL and true when only one is.[4] Prefer IS NOT DISTINCT FROM to = when matching two columns that may both be missing and a missing pair should count as a match. (Meridian's view) In DuckDB, IS NOT DISTINCT FROM performs an equality comparison in which NULL values compare equal to each other.[11]

In PostgreSQL, p IS NOT TRUE is true when p is false or unknown.[4] In PostgreSQL, NOT applied to NULL gives NULL.[10] In PostgreSQL, IS UNKNOWN and IS NOT UNKNOWN are effectively IS NULL and IS NOT NULL, except that the input must be of Boolean type.[4] Where a filter should keep the rows whose condition is false or unknown, write p IS NOT TRUE rather than NOT p. (Meridian's view)

Sources

  1. [1]Boolean expression - Wikipediasecondary source
  2. [2]Null (SQL) - Wikipediasecondary source
  3. [3]Boolean Type – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.2. Comparison Functions and Operatorsdocumentation
  5. [5]IN Operator – DuckDBdocumentation
  6. [6]Logical Operators – DuckDBdocumentation
  7. [7]Comparisons – DuckDBdocumentation
  8. [8]Boolean algebra - Wikipediasecondary source
  9. [9]Three-valued logic - Wikipediasecondary source
  10. [10]PostgreSQL: Documentation: 18: 9.1. Logical Operatorsdocumentation
  11. [11]NULL Values – DuckDBdocumentation

Used in

One Protocol uses this operation: