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

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 │ ᴺᵁᴸᴸ                                                      │
└──────────┴───────────────────────────────────────────────────────────┘

firstNonDefault

Introduced in: v25.9

Returns the first non-default value from a set of arguments

Syntax

Arguments

• arg1 — The first argument to check - arg2 — The second argument to check - ... — Additional arguments to check

Returned value

Result type is the supertype of all arguments

Examples

integers

SELECT firstNonDefault(0, 1, 2)

1

strings

SELECT firstNonDefault('', 'hello', 'world')

'hello'

nulls

SELECT firstNonDefault(NULL, 0 :: UInt8, 1 :: UInt8)

1

nullable zero

SELECT firstNonDefault(NULL, 0 :: Nullable(UInt8), 1 :: Nullable(UInt8))

0

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                 │
└──────────────────┴───────────────────┘

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)            │
└────────────────┴────────────────────────────┘