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 if x is NULL .
• 0 if x is not NULL .

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

• 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 .

See also operator IS NOT NULL .

isNotNull(x)

Arguments:

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

Returned value

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

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.