ReferenceSQL ReferenceFunctions

Null

Null functions reference.

This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See SQL interface scope.

assumeNotNull

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 t_null;
SELECT toTypeName(assumeNotNull(y)) FROM t_null;
┌─assumeNotNull(y)─┐
│                0 │
│                3 │
└──────────────────┘
┌─toTypeName(assumeNotNull(y))─┐
│ Int8                         │
│ Int8                         │
└──────────────────────────────┘

Introduced in version 1.1.0.

coalesce

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

Introduced in version 1.1.0.

firstNonDefault

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

Syntax

firstNonDefault(arg1[, arg2[ ...]])

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

Introduced in version 25.9.0.

ifNull

Returns an alternative value if the first argument is NULL.

The operator x ?? alt is the same function. It binds tighter than a comparison and looser than arithmetic and ||: x ?? 0 = 1 is (x ?? 0) = 1, and x ?? a || b is x ?? (a || b). It chains to the left: a ?? b ?? c is ifNull(ifNull(a, b), c). It gives a value to a Dynamic or JSON path that may be missing: json.price::Nullable(Float64) ?? 0.

Syntax

ifNull(x, alt)
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                 │
└──────────────────┴───────────────────┘

The ?? operator

SELECT NULL ?? 1, 2 ?? 1, NULL ?? NULL ?? 3;
┌─ifNull(NULL, 1)─┬─ifNull(2, 1)─┬─ifNull(ifNull(NULL, NULL), 3)─┐
│               1 │            2 │                             3 │
└─────────────────┴──────────────┴───────────────────────────────┘

Introduced in version 1.1.0.

isNotNull

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

Introduced in version 1.1.0.

isNull

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

Introduced in version 1.1.0.

isNullable

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

Introduced in version 22.7.0.

isZeroOrNull

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

Introduced in version 20.3.0.

nullIf

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

Introduced in version 1.1.0.

toNullable

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

Introduced in version 1.1.0.