Functions for Working with Nullable Values
isNullβ
Returns whether the argument is NULL.
See also operator IS NULL
.
Syntax
isNull(x)
Alias: ISNULL
.
Arguments
x
β A value of non-compound data type.
Returned value
1
ifx
isNULL
.0
ifx
is notNULL
.
Example
Table:
ββxββ¬ββββyββ
β 1 β α΄Ία΅α΄Έα΄Έ β
β 2 β 3 β
βββββ΄βββββββ
Query:
SELECT x FROM t_null WHERE isNull(y);
Result:
ββxββ
β 1 β
βββββ
isNullableβ
Returns 1
if a column is Nullable (i.e allows NULL
values), 0
otherwise.
Syntax
isNullable(x)
Arguments
x
β column.
Returned value
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.
See also operator IS NOT NULL
.
isNotNull(x)
Arguments:
x
β A value of non-compound data type.
Returned value
1
ifx
is notNULL
.0
ifx
isNULL
.
Example
Table:
ββxββ¬ββββyββ
β 1 β α΄Ία΅α΄Έα΄Έ β
β 2 β 3 β
βββββ΄βββββββ
Query:
SELECT x FROM t_null WHERE isNotNull(y);
Result:
ββxββ
β 2 β
βββββ
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
.
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
whenx
andy
are bothNULL
.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
ifx
is 0 (zero) orNULL
.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 areNULL
.
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 forNULL
.alt
β The value that the function returns ifx
isNULL
.
Returned values
x
ifx
is notNULL
.alt
ifx
isNULL
.
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 notNULL
. - 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) β
ββββββββββββββββββββββββββββββ