Functions for Working with Nullable Values

    Alias: .

    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

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

    Query

    1. SELECT x FROM t_null WHERE isNull(y)
    1. ┌─x─┐
    2. │ 1 │
    3. └───┘

    isNotNull

    Checks whether the argument is .

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

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

    Query

    1. SELECT x FROM t_null WHERE isNotNull(y)
    1. ┌─x─┐
    2. │ 2 │
    3. └───┘

      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.

      1. ┌─name─────┬─mail─┬─phone─────┬──icq─┐
      2. │ client 1 │ ᴺᵁᴸᴸ │ 123-45-67 │  123 │
      4. └──────────┴──────┴───────────┴──────┘

      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:

      1. SELECT coalesce(mail, phone, CAST(icq,'Nullable(String)')) FROM aBook

      ifNull

      Returns an alternative value if the main argument is NULL.

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

      1. SELECT ifNull('a', 'b')
      1. ┌─ifNull('a', 'b')─┐
      2. │ a                │
      3. └──────────────────┘
      1. SELECT ifNull(NULL, 'b')
      1. ┌─ifNull(NULL, 'b')─┐
      2. │ b                 │
      3. └───────────────────┘

      Returns NULL if the arguments are equal.

      1. nullIf(x, y)

      Arguments:

      Returned values

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

      Example

      1. SELECT nullIf(1, 1)
      1. ┌─nullIf(1, 1)─┐
      2. │         ᴺᵁᴸᴸ │
      3. └──────────────┘
      1. SELECT nullIf(1, 2)
      2. └──────────────┘

      assumeNotNull

      Results in a value of type Nullable for a non- Nullable, if the value is not NULL.

      Arguments:

      • x — The original value.

      Returned values

      • The original value from the non-Nullable type, if it is not NULL.
      • The default value for the non-Nullable type if the original value was NULL.

      Example

      Consider the t_null table.

      1. SHOW CREATE TABLE t_null
      1. ┌─statement─────────────────────────────────────────────────────────────────┐
      2. │ CREATE TABLE default.t_null ( x Int8,  y Nullable(Int8)) ENGINE = TinyLog │
      3. └───────────────────────────────────────────────────────────────────────────┘
      1. ┌─x─┬────y─┐
      2. │ 1 │ ᴺᵁᴸᴸ │
      3. │ 2 │    3 │
      4. └───┴──────┘

      Apply the assumeNotNull function to the y column.

      1. SELECT assumeNotNull(y) FROM t_null
      1. ┌─assumeNotNull(y)─┐
      2. │                0 │
      3. │                3 │
      4. └──────────────────┘
      1. SELECT toTypeName(assumeNotNull(y)) FROM t_null
      1. ┌─toTypeName(assumeNotNull(y))─┐
      2. │ Int8                         │
      3. │ Int8                         │
      4. └──────────────────────────────┘

      Converts the argument type to Nullable.

      1. toNullable(x)

      Arguments:

      • x — The value of any non-compound type.

      Returned value

      • The input value with a Nullable type.

      Example

      1. SELECT toTypeName(10)
      1. ┌─toTypeName(10)─┐
      2. │ UInt8          │
      3. └────────────────┘
      1. ┌─toTypeName(toNullable(10))─┐
      2. └────────────────────────────┘