# NULL handling functions

> 7 NULL handling functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

7 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

| Function | Summary |
| --- | --- |
| [`assume_not_null`](#assume_not_null) | Removes a value’s nullable annotation after enforcing non-NULL validity. |
| [`coalesce`](#coalesce) | Returns the earliest available non-NULL value from a compatible argument list. |
| [`if_null`](#if_null) | Substitutes a default only when the primary expression is SQL NULL. |
| [`is_not_null`](#is_not_null) | Recognizes present SQL values, including zero and empty text. |
| [`is_null`](#is_null) | Detects SQL absence without conflating it with JSON null or empty values. |
| [`is_zero_or_null`](#is_zero_or_null) | Accepts either numeric zero or SQL NULL as a true predicate result. |
| [`null_if`](#null_if) | Converts a matching first value into SQL NULL while preserving unequal inputs. |

## assume_not_null

Removes a value’s nullable annotation after enforcing non-NULL validity.

```text
assume_not_null(value:nullable(T)) -> T
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `T` |
| Aliases | `assumeNotNull` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL is an error; otherwise strip nullability |

Returns the original value through a nonnullable result descriptor when a proof or runtime check establishes its presence. An actual NULL raises INVALID_ARGUMENT rather than producing an arbitrary default. The operation changes nullability metadata without converting the underlying logical type.

**Example**

```sql
assume_not_null(7)
-- → 7
```

## coalesce

Returns the earliest available non-NULL value from a compatible argument list.

```text
coalesce(x1:T,x2:T,...) -> common_supertype(T...) nullable
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `common_supertype(T...) nullable` |
| Aliases | `if_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | first_non_NULL_argument;all_NULL_returns_NULL |

Checks two through sixteen arguments in order and stops at the first value that is not SQL NULL. All-NULL input returns a typed NULL, and later expressions are masked after a value is found. Incompatible arity raises INVALID_ARGUMENT and a missing lossless common type raises TYPE_MISMATCH.

**Example**

```sql
coalesce(NULL, 7, 9)
-- → 7
```

## if_null

Substitutes a default only when the primary expression is SQL NULL.

```text
if_null(x:T,default:T) -> common_supertype(T,T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `common_supertype(T,T)` |
| Aliases | `ifNull` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL_x_selects_default;otherwise_x |

Returns the first argument whenever it is present, evaluating the default only for NULL rows. A NULL default remains NULL if selected, and incompatible result types raise TYPE_MISMATCH. This binary operation preserves zero, false and empty strings as ordinary primary values.

**Example**

```sql
if_null(NULL, 12)
-- → 12
```

## is_not_null

Recognizes present SQL values, including zero and empty text.

```text
is_not_null(x:T) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `bool` |
| Aliases | `isNotNull` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | returns_false_exactly_for_SQL_NULL |

Returns false exactly for SQL NULL and true for every non-NULL value of a supported ledger type. It never returns NULL, and JSON null remains a present JSON value rather than SQL absence. Binding a nonvalue expression instead of one typed argument raises TYPE_MISMATCH.

**Example**

```sql
is_not_null('')
-- → true
```

## is_null

Detects SQL absence without conflating it with JSON null or empty values.

```text
is_null(x:T) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `bool` |
| Aliases | `isNull` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | returns_true_exactly_for_SQL_NULL |

Tests the SQL nullability flag independently of the value’s payload or logical type. The Boolean result is always present, including when the input is NULL. Empty arrays, empty strings and native JSON null return false; incompatible call shapes raise TYPE_MISMATCH.

**Example**

```sql
is_null(NULL)
-- → true
```

## is_zero_or_null

Accepts either numeric zero or SQL NULL as a true predicate result.

```text
is_zero_or_null(value:nullable(number)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `bool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | true for NULL or numeric zero; false otherwise |

Returns true for a missing numeric value or for a value equal to zero, including floating negative zero. Every other number returns false and the predicate itself is never NULL. Nonnumeric input raises TYPE_MISMATCH; a floating NaN is not equal to zero.

**Example**

```sql
is_zero_or_null(0)
-- → true
```

## null_if

Converts a matching first value into SQL NULL while preserving unequal inputs.

```text
nullif(x:T,y:T) -> T nullable
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `T nullable` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | equal_non_NULL_inputs_return_NULL;otherwise_x |

Compares two values with SQL equality and returns NULL only when that comparison is true. If the second argument is NULL, a present first argument survives because the comparison is unknown. The input type must support equality or raise TYPE_MISMATCH; a NULL first argument remains NULL.

**Example**

```sql
nullif(8, 8)
-- → NULL
```

---

Source: https://nyxdb.io/docs/functions/null-handling/