# Functions for Working with Nullable Values

## isNullβ

Checks whether the argument is NULL.

isNull(x)

Alias: ISNULL.

Arguments

• x β A value with a non-compound data type.

Returned value

• 1 if x is NULL.
• 0 if x is not NULL.

Example

Input table

ββxββ¬ββββyββ
β 1 β α΄Ία΅α΄Έα΄Έ β
β 2 β 3 β
βββββ΄βββββββ

Query

SELECT x FROM t_null WHERE isNull(y);
ββxββ
β 1 β
βββββ

## isNotNullβ

Checks whether the argument is NULL.

isNotNull(x)

Arguments:

• x β A value with a non-compound data type.

Returned value

• 0 if x is NULL.
• 1 if x is not NULL.

Example

Input table

ββxββ¬ββββyββ
β 1 β α΄Ία΅α΄Έα΄Έ β
β 2 β 3 β
βββββ΄βββββββ

Query

SELECT x FROM t_null WHERE isNotNull(y);
ββxββ
β 2 β
βββββ

## coalesceβ

Checks from left to right whether NULL arguments were passed and returns the first non-NULL argument.

coalesce(x,...)

Arguments:

• Any number of parameters of a non-compound type. All parameters must be compatible by data type.

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ββββββ¬ββicqββ
β client 1 β α΄Ία΅α΄Έα΄Έ β 123-45-67 β 123 β
β client 2 β α΄Ία΅α΄Έα΄Έ β α΄Ία΅α΄Έα΄Έ β α΄Ία΅α΄Έα΄Έ β
ββββββββββββ΄βββββββ΄ββββββββββββ΄βββββββ

The mail and phone fields are of type String, but the icq 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(icq,'Nullable(String)')) FROM aBook;
ββnameββββββ¬βcoalesce(mail, phone, CAST(icq, 'Nullable(String)'))ββ
β client 1 β 123-45-67 β
β client 2 β α΄Ία΅α΄Έα΄Έ β
ββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββββ

## ifNullβ

Returns an alternative value if the main 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

• The value x, if x is not NULL.
• The value alt, if x is NULL.

Example

SELECT ifNull('a', 'b');
ββifNull('a', 'b')ββ
β a β
ββββββββββββββββββββ
SELECT ifNull(NULL, 'b');
ββifNull(NULL, 'b')ββ
β b β
βββββββββββββββββββββ

## nullIfβ

Returns NULL if the arguments are equal.

nullIf(x, y)

Arguments:

x, y β Values for comparison. They must be compatible types, or ClickHouse will generate an exception.

Returned values

• NULL, if the arguments are equal.
• The x value, if the arguments are not equal.

Example

SELECT nullIf(1, 1);
ββnullIf(1, 1)ββ
β α΄Ία΅α΄Έα΄Έ β
ββββββββββββββββ
SELECT nullIf(1, 2);
ββnullIf(1, 2)ββ
β 1 β
ββββββββββββββββ

## assumeNotNullβ

Results in an equivalent non-Nullable value for a Nullable type. In case the original value is NULL the result is undetermined. See also ifNull and coalesce functions.

assumeNotNull(x)

Arguments:

• x β The original value.

Returned values

• The original value from the non-Nullable type, if it is not NULL.
• Implementation specific result if the original value was NULL.

Example

Consider the t_null table.

SHOW CREATE TABLE t_null;
ββstatementββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β CREATE TABLE default.t_null ( x Int8, y Nullable(Int8)) ENGINE = TinyLog β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
ββxββ¬ββββyββ
β 1 β α΄Ία΅α΄Έα΄Έ β
β 2 β 3 β
βββββ΄βββββββ

Apply the assumeNotNull function to the y column.

SELECT assumeNotNull(y) FROM t_null;
ββassumeNotNull(y)ββ
β 0 β
β 3 β
ββββββββββββββββββββ
SELECT toTypeName(assumeNotNull(y)) FROM t_null;
ββtoTypeName(assumeNotNull(y))ββ
β Int8 β
β Int8 β
ββββββββββββββββββββββββββββββββ

## toNullableβ

Converts the argument type to Nullable.

toNullable(x)

Arguments:

• x β The value of any non-compound type.

Returned value

• The input value with a Nullable type.

Example

SELECT toTypeName(10);
ββtoTypeName(10)ββ
β UInt8 β
ββββββββββββββββββ
SELECT toTypeName(toNullable(10));
ββtoTypeName(toNullable(10))ββ
β Nullable(UInt8) β
ββββββββββββββββββββββββββββββ