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 β
ββββββββββββββ