Functions for working with nullable values

isNull

Returns whether the argument is NULL.

isNullable

Returns 1 if a column is Nullable (i.e allows NULL values), 0 otherwise.

Syntax

isNullable(x)

Arguments

x — column.

Returned value

1 if x allows NULL values. UInt8.
0 if x does not allow NULL values. UInt8.

Example

Query:

CREATE TABLE tab (ordinary_col UInt32, nullable_col Nullable(UInt32)) ENGINE = Log;
INSERT INTO tab (ordinary_col, nullable_col) VALUES (1,1), (2, 2), (3,3);
SELECT isNullable(ordinary_col), isNullable(nullable_col) FROM tab;

Result:

   ┌───isNullable(ordinary_col)──┬───isNullable(nullable_col)──┐
1. │                           0 │                           1 │
2. │                           0 │                           1 │
3. │                           0 │                           1 │
   └─────────────────────────────┴─────────────────────────────┘

isNotNull

Returns whether the argument is not NULL.

isNotDistinctFrom

Performs null-safe comparison. Used to compare JOIN keys which contain NULL values in the JOIN ON section. 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.

Note

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

Syntax

isNotDistinctFrom(x, y)

Arguments

x — first JOIN key.
y — second JOIN key.

Returned value

true when x and y are both NULL.
false otherwise.

Example

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

isZeroOrNull

Returns whether the argument is 0 (zero) or NULL.

isZeroOrNull(x)

Arguments:

x — A value of non-compound data type.

Returned value

1 if x is 0 (zero) or NULL.
0 else.

Example

Table:

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    0 │
│ 3 │    3 │
└───┴──────┘

Query:

SELECT x FROM t_null WHERE isZeroOrNull(y);

Result:

┌─x─┐
│ 1 │
│ 2 │
└───┘

coalesce

Returns the leftmost non-NULL argument.

coalesce(x,...)

Arguments:

Any number of parameters of non-compound type. All parameters must be of mutually compatible data types.

Returned values

The first non-NULL argument
NULL, if all arguments are NULL.

Example

Consider a list of contacts that may specify multiple ways to contact a customer.

┌─name─────┬─mail─┬─phone─────┬──telegram─┐
│ client 1 │ ᴺᵁᴸᴸ │ 123-45-67 │       123 │
│ client 2 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ      │      ᴺᵁᴸᴸ │
└──────────┴──────┴───────────┴───────────┘

The mail and phone fields are of type String, but the telegram field is UInt32, so it needs to be converted to String.

Get the first available contact method for the customer from the contact list:

SELECT name, coalesce(mail, phone, CAST(telegram,'Nullable(String)')) FROM aBook;

┌─name─────┬─coalesce(mail, phone, CAST(telegram, 'Nullable(String)'))─┐
│ client 1 │ 123-45-67                                                 │
│ client 2 │ ᴺᵁᴸᴸ                                                      │
└──────────┴───────────────────────────────────────────────────────────┘

ifNull

Returns an alternative value if the argument is NULL.

ifNull(x, alt)

Arguments:

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

Returned values

x if x is not NULL.
alt if x is NULL.

Example

Query:

SELECT ifNull('a', 'b');

Result:

┌─ifNull('a', 'b')─┐
│ a                │
└──────────────────┘

Query:

SELECT ifNull(NULL, 'b');

Result:

┌─ifNull(NULL, 'b')─┐
│ b                 │
└───────────────────┘

nullIf

Returns NULL if both arguments are equal.

nullIf(x, y)

Arguments:

x, y — Values to compare. Must be of compatible types.

Returned values

NULL if the arguments are equal.
x if the arguments are not equal.

Example

Query:

SELECT nullIf(1, 1);

Result:

┌─nullIf(1, 1)─┐
│         ᴺᵁᴸᴸ │
└──────────────┘

Query:

SELECT nullIf(1, 2);

Result:

┌─nullIf(1, 2)─┐
│            1 │
└──────────────┘

assumeNotNull

Returns the corresponding non-Nullable value for a value of Nullable type. If the original value is NULL, an arbitrary result can be returned. See also functions ifNull and coalesce.

assumeNotNull(x)

Arguments:

x — The original value.

Returned values

The input value as non-Nullable type, if it is not NULL.
An arbitrary value, if the input value is NULL.

Example

Table:


┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘

Query:

SELECT assumeNotNull(y) FROM table;

Result:

┌─assumeNotNull(y)─┐
│                0 │
│                3 │
└──────────────────┘

Query:

SELECT toTypeName(assumeNotNull(y)) FROM t_null;

Result:

┌─toTypeName(assumeNotNull(y))─┐
│ Int8                         │
│ Int8                         │
└──────────────────────────────┘

toNullable

Converts the argument type to Nullable.

toNullable(x)

Arguments:

x — A value of non-compound type.

Returned value

The input value but of Nullable type.

Example

Query:

SELECT toTypeName(10);

Result:

┌─toTypeName(10)─┐
│ UInt8          │
└────────────────┘

Query:

SELECT toTypeName(toNullable(10));

Result:

┌─toTypeName(toNullable(10))─┐
│ Nullable(UInt8)            │
└────────────────────────────┘

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

assumeNotNull(x)

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

CREATE TABLE t_null (x Int8, y Nullable(Int8))
ENGINE=MergeTree()
ORDER BY x;

INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT assumeNotNull(y) FROM table;
SELECT toTypeName(assumeNotNull(y)) FROM t_null;

┌─assumeNotNull(y)─┐
│                0 │
│                3 │
└──────────────────┘
┌─toTypeName(assumeNotNull(y))─┐
│ Int8                         │
│ Int8                         │
└──────────────────────────────┘

coalesce

Introduced in: v1.1

Returns the leftmost non-NULL argument.

Syntax

coalesce(x[, y, ...])

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

-- Consider a list of contacts that may specify multiple ways to contact a customer.

CREATE TABLE aBook
(
    name String,
    mail Nullable(String),
    phone Nullable(String),
    telegram Nullable(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO aBook VALUES ('client 1', NULL, '123-45-67', 123), ('client 2', NULL, NULL, NULL);

-- The mail and phone fields are of type String, but the telegram field is UInt32 so it needs to be converted to String.

-- Get the first available contact method for the customer from the contact list

SELECT name, coalesce(mail, phone, CAST(telegram,'Nullable(String)')) FROM aBook;

┌─name─────┬─coalesce(mail, phone, CAST(telegram, 'Nullable(String)'))─┐
│ client 1 │ 123-45-67                                                 │
│ client 2 │ ᴺᵁᴸᴸ                                                      │
└──────────┴───────────────────────────────────────────────────────────┘

ifNull

Introduced in: v1.1

Returns an alternative value if the first argument is NULL.

Syntax

ifNull(x, alt)

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

SELECT ifNull('a', 'b'), ifNull(NULL, 'b');

┌─ifNull('a', 'b')─┬─ifNull(NULL, 'b')─┐
│ a                │ b                 │
└──────────────────┴───────────────────┘

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

isNotDistinctFrom(x, y)

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

isNotNull(x)

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

CREATE TABLE t_null
(
  x Int32,
  y Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT x FROM t_null WHERE isNotNull(y);

┌─x─┐
│ 2 │
└───┘

isNull

Introduced in: v1.1

Checks if the argument is NULL.

Also see: operator IS NULL.

Syntax

isNull(x)

Arguments

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

Returned value

Returns 1 if x is NULL, otherwise 0. UInt8

Examples

Usage example

CREATE TABLE t_null
(
  x Int32,
  y Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT x FROM t_null WHERE isNull(y);

┌─x─┐
│ 1 │
└───┘

isNullable

Introduced in: v22.7

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

Syntax

isNullable(x)

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

CREATE TABLE tab (
    ordinary_col UInt32,
    nullable_col Nullable(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO tab (ordinary_col, nullable_col) VALUES (1,1), (2, 2), (3,3);
SELECT isNullable(ordinary_col), isNullable(nullable_col) FROM tab;

┌───isNullable(ordinary_col)──┬───isNullable(nullable_col)──┐
│                           0 │                           1 │
│                           0 │                           1 │
│                           0 │                           1 │
└─────────────────────────────┴─────────────────────────────┘

isZeroOrNull

Introduced in: v20.3

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

Syntax

isZeroOrNull(x)

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

CREATE TABLE t_null
(
  x Int32,
  y Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO t_null VALUES (1, NULL), (2, 0), (3, 3);

SELECT x FROM t_null WHERE isZeroOrNull(y);

┌─x─┐
│ 1 │
│ 2 │
└───┘

nullIf

Introduced in: v1.1

Returns NULL if both arguments are equal.

Syntax

nullIf(x, y)

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

SELECT nullIf(1, 1), nullIf(1, 2);

┌─nullIf(1, 1)─┬─nullIf(1, 2)─┐
│         ᴺᵁᴸᴸ │            1 │
└──────────────┴──────────────┘

toNullable

Introduced in: v1.1

Converts the provided argument type to Nullable.

Syntax

toNullable(x)

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

SELECT toTypeName(10), toTypeName(toNullable(10));

┌─toTypeName(10)─┬─toTypeName(toNullable(10))─┐
│ UInt8          │ Nullable(UInt8)            │
└────────────────┴────────────────────────────┘

isNull​

isNullable​

isNotNull​

isNotDistinctFrom​

isZeroOrNull​

coalesce​

ifNull​

nullIf​

assumeNotNull​

toNullable​

assumeNotNull​

coalesce​

ifNull​

isNotDistinctFrom​

isNotNull​

isNull​

isNullable​

isZeroOrNull​

nullIf​

toNullable​

isNull

isNullable

isNotNull

isNotDistinctFrom

isZeroOrNull

coalesce

ifNull

nullIf

assumeNotNull

toNullable

assumeNotNull

coalesce

ifNull

isNotDistinctFrom

isNotNull

isNull

isNullable

isZeroOrNull

nullIf

toNullable