Working with none

Where none comes from

An optional column holds none when an insert omits it or writes the none literal explicitly - the two are equivalent. Run the snippets on this page in order:

Every non-Option column rejects none at write time. There is no "forgot the NOT NULL constraint" failure mode - required is the default, and a missing value in a required column is an error (CONSTRAINT_007) that rolls back the whole request:

Besides optional columns, none is produced by queries themselves: unmatched rows in a left join, aggregates over groups with nothing to aggregate, and any expression whose input is missing. Each case is covered below. See Tables for the rest of the column model.

Propagation through expressions

Arithmetic, comparisons, and scalar functions propagate none instead of failing or inventing a value: if an input is missing, the result is missing. Because none carries its type, the expression still type-checks - price * 2 on a missing price is a missing number, not an error:

Logical operators are the deliberate exception. When one side already decides the answer, the missing side cannot change it, so the result is definite: none and false is false, none or true is true. Only when the answer genuinely depends on the missing value does none come through:

Testing for none

none is the absence of a value, so comparing anything to it - including another none - never produces true. A filter on == none matches nothing:

The functions is::none and is::some exist for exactly this: they always return a definite true or false, never none. They are RQL's counterpart to SQL's IS NULL and IS NOT NULL (see RQL for SQL users):

Filters keep only definite matches

A filter keeps a row only when its predicate is definitely true. A predicate that evaluates to none drops the row, the same as false:

The consequence worth internalizing: a predicate and its negation do not split the data into two halves. not (none > 50) is still none, so the row with the missing score matches neither side. If "the rows where the predicate did not hold" must include missing values, say so with is::none:

Replacing none

To substitute a default, use a match expression with an is::none arm. Note that a value match (match nickname { ... }) cannot do this - its arms compare by equality, and equality never matches none:

The write side mirrors it: assigning none to an optional column in an update clears the stored value:

Joins introduce none

A left join keeps every left row; where the right side has no match, all of its columns are none - even columns whose source table declares them as required. Any column that arrives via a left join can therefore be missing:

That makes is::none on a joined column the idiom for "rows without a match":

Aggregates skip it, group keys keep it

Aggregate functions (math::sum, math::avg, math::min, math::max, math::count) ignore none inputs - a missing reading does not drag an average down or a count up. math::count counts defined values, so counting an optional column and counting a required column answer different questions. A group whose inputs are all missing has nothing to aggregate, and its result is none:

As a group key, none behaves differently: all rows with a missing key land in one group of their own rather than being discarded. Grouping never loses rows:

Sorting and output

Sorting ranks none above every defined value: ascending order puts missing values last, descending order puts them first.

In rendered tables - the CLI, the docs snippets on this page - a missing value prints as ⟪none⟫, deliberately unmistakable for a string.