Nullable(typename)

Allows to store special marker (NULL) that denotes “missing value” alongside normal values allowed by TypeName. For example, a Nullable(Int8) type column can store Int8 type values, and the rows that do not have a value will store NULL.

For a TypeName, you can’t use composite data types Array and Tuple. Composite data types can contain Nullable type values, such as Array(Nullable(Int8)).

A Nullable type field can’t be included in table indexes.

NULL is the default value for any Nullable type, unless specified otherwise in the ClickHouse server configuration.

Storage Features

To store Nullable type values in a table column, ClickHouse uses a separate file with NULL masks in addition to normal file with values. Entries in masks file allow ClickHouse to distinguish between NULL and a default value of corresponding data type for each table row. Because of an additional file, Nullable column consumes additional storage space compared to a similar normal one.

note

Using Nullable almost always negatively affects performance, keep this in mind when designing your databases.

Finding NULL

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

Example

Query:

CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO nullable VALUES (1) (NULL) (2) (NULL);

SELECT n.null FROM nullable;

Result:

┌─n.null─┐
│      0 │
│      1 │
│      0 │
│      1 │
└────────┘

Usage Example

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog

INSERT INTO t_null VALUES (1, NULL), (2, 3)

SELECT x + y FROM t_null

┌─plus(x, y)─┐
│       ᴺᵁᴸᴸ │
│          5 │
└────────────┘

Original article