Nullable(T)

The Nullable(T) data type allows a column to store a special NULL marker, representing a missing or undefined value, in addition to the standard values of its base type T. For instance, a Nullable(Int8) column can hold Int8 integers or NULL. Note that T cannot be a composite type like Array, Map, or Tuple directly, but these composite types can contain Nullable elements (e.g., Array(Nullable(Int8))). Nullable fields cannot be used in table indexes, and NULL is the default value for any Nullable type.

Finding NULL

You can find NULL values in a column by using the .null subcolumn without reading the whole column. It returns 1 if the corresponding value is NULL and 0 otherwise.

Example

SELECT
    value_col,
    value_col.null AS is_null_flag
FROM (
    SELECT CAST(1 AS Nullable(UInt32)) AS value_col
    UNION ALL
    SELECT CAST(NULL AS Nullable(UInt32)) AS value_col
    UNION ALL
    SELECT CAST(2 AS Nullable(UInt32)) AS value_col
    UNION ALL
    SELECT CAST(NULL AS Nullable(UInt32)) AS value_col
)

Result:

┌─value_col─┬─is_null_flag─┐
│         1 │            0 │
│      ᴺᵁᴸᴸ │            1 │
│         2 │            0 │
│      ᴺᵁᴸᴸ │            1 │
└───────────┴──────────────┘

General Usage Example

When performing operations with Nullable values, NULL typically propagates through expressions.

SELECT
    x,
    y,
    x + y AS sum_result
FROM (
    SELECT 1 AS x, CAST(NULL AS Nullable(Int8)) AS y
    UNION ALL
    SELECT 2 AS x, CAST(3 AS Nullable(Int8)) AS y
)

Result:

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