Skip to main content
Skip to main content
Edit this page

Nullable(T)

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

T can't be any of the following composite data types:

  • Array — Not supported
  • Map — Not supported
  • Tuple — Experimental support available*

However, composite data types can contain Nullable type values, e.g. Array(Nullable(Int8)) or Tuple(Nullable(String), Nullable(Int64)).

Experimental: Nullable Tuples

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