Skip to main content
Skip to main content
Edit this page

Functions for working with nullable values

assumeNotNull​

Introduced in: v1.1

Returns the corresponding non-Nullable value for a value of type Nullable. If the original value is NULL, an arbitrary result can be returned.

See also: functions ifNull and coalesce.

Syntax

Arguments

  • x — The original value of any nullable type. Nullable(T)

Returned value

Returns the non-nullable value, if the original value was not NULL, otherwise an arbitrary value, if the input value is NULL. Any

Examples

Usage example

coalesce​

Introduced in: v1.1

Returns the leftmost non-NULL argument.

Syntax

Arguments

  • x[, y, ...] — Any number of parameters of non-compound type. All parameters must be of mutually compatible data types. Any

Returned value

Returns the first non-NULL argument, otherwise NULL, if all arguments are NULL. Any or NULL

Examples

Usage example

ifNull​

Introduced in: v1.1

Returns an alternative value if the first argument is NULL.

Syntax

Arguments

  • x — The value to check for NULL. Any
  • alt — The value that the function returns if x is NULL. Any

Returned value

Returns the value of x if it is not NULL, otherwise alt. Any

Examples

Usage example

isNotDistinctFrom​

Introduced in: v23.8

Performs a null-safe comparison between two JOIN keys. This function will consider two NULL values as identical and will return true, which is distinct from the usual equals behavior where comparing two NULL values would return NULL.

Info

This function is an internal function used by the implementation of JOIN ON. Please do not use it manually in queries.

For a complete example see: NULL values in JOIN keys.

Syntax

Arguments

  • x — First JOIN key to compare. Any
  • y — Second JOIN key to compare. Any

Returned value

Returns true when x and y are both NULL, otherwise false. Bool

Examples

isNotNull​

Introduced in: v1.1

Checks if the argument is not NULL.

Also see: operator IS NOT NULL.

Syntax

Arguments

  • x — A value of non-compound data type. Any

Returned value

Returns 1 if x is not NULL, otherwise 0. UInt8

Examples

Usage example

isNull​

Introduced in: v1.1

Checks if the argument is NULL.

Also see: operator IS NULL.

Syntax

Arguments

  • x — A value of non-compound data type. Any

Returned value

Returns 1 if x is NULL, otherwise 0. UInt8

Examples

Usage example

isNullable​

Introduced in: v22.7

Checks whether the argument's data type is Nullable (i.e it allows NULL values).

Syntax

Arguments

  • x — A value of any data type. Any

Returned value

Returns 1 if x is of a Nullable data type, otherwise 0. UInt8

Examples

Usage example

isZeroOrNull​

Introduced in: v20.3

Checks if the argument is either zero (0) or NULL.

Syntax

Arguments

  • x — A numeric value. UInt

Returned value

Returns 1 if x is NULL or equal to zero, otherwise 0. UInt8/16/32/64 or Float32/Float64

Examples

Usage example

nullIf​

Introduced in: v1.1

Returns NULL if both arguments are equal.

Syntax

Arguments

  • x — The first value. Any
  • y — The second value. Any

Returned value

Returns NULL if both arguments are equal, otherwise returns the first argument. NULL or Nullable(x)

Examples

Usage example

toNullable​

Introduced in: v1.1

Converts the provided argument type to Nullable.

Syntax

Arguments

  • x — A value of any non-compound type. Any

Returned value

Returns the input value but of Nullable type. Nullable(Any)

Examples

Usage example