Nullable 函数

请注意:
下文中的一些示例引用自 ClickHouse 社区文档并经过一定修改确保可以在 ByteHouse 中正常使用。

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.

Syntax

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

CREATE TABLE IF NOT EXISTS test.functionAssumeNotNull ( x Int8,  y Nullable(Int8)) ENGINE = CnchMergeTree ORDER BY x;
INSERT INTO test.functionAssumeNotNull VALUES (1,NULL),(2,3);
SELECT * FROM test.functionAssumeNotNull;

┌─x─┬─y────┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 3    │
└───┴──────┘

Apply the assumeNotNull function to the y column.

SELECT assumeNotNull(y),toTypeName(assumeNotNull(y))  FROM test.functionAssumeNotNull;

┌─assumeNotNull(y)─┬─toTypeName(assumeNotNull(y))─┐
│ 0                │ Int8                         │
│ 3                │ Int8                         │
└──────────────────┴──────────────────────────────┘

coalesce

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

Syntax

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.

CREATE TABLE IF NOT EXISTS test.functionCoalesce (name String, mail Nullable(String), phone Nullable(String), icq Nullable(UInt32)) ENGINE=CnchMergeTree ORDER BY name;
INSERT INTO test.functionCoalesce VALUES ('client 1', NULL, '123-45-67', 123), ('client 2', NULL, NULL, NULL);
SELECT * FROM test.functionCoalesce;

┌─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 test.functionCoalesce;

┌─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 .

Syntax

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

isNotNull

Checks whether the argument is NULL.

Syntax

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

CREATE TABLE IF NOT EXISTS test.functionIsNotNull (x UInt8, y Nullable(UInt8)) ENGINE=CnchMergeTree ORDER BY x;
INSERT INTO test.functionIsNotNull VALUES (1, NULL),(2,3);
SELECT * FROM test.functionIsNotNull;

┌─x─┬─y────┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 3    │
└───┴──────┘

SELECT x FROM test.functionIsNotNull WHERE isNotNull(y);

┌─x─┐
│ 2 │
└───┘

isNull

Checks whether the argument is NULL.

Syntax

isNull(x)

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

CREATE TABLE IF NOT EXISTS test.functionIsNull (x UInt8, y Nullable(UInt8)) ENGINE=CnchMergeTree ORDER BY x;
INSERT INTO test.functionIsNull VALUES (1, NULL),(2,3);
SELECT * FROM test.functionIsNull;

┌─x─┬─y────┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 3    │
└───┴──────┘

SELECT x FROM test.functionIsNull WHERE isNull(y);

┌─x─┐
│ 1 │
└───┘

nullIf

Returns NULL if the arguments are equal.

Syntax

nullIf(x, y)

Arguments

x , y — Values for comparison. They must be compatible types, or Bytehouse 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            │
└──────────────┘

toNullable

Converts the argument type to Nullable .

Syntax

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