ClickHouse は以前は null が扱えず、最近扱えるようになりました。
まだドキュメントなどに十分反映されていないため、使い方についてまとめてみました。

型の宣言・テーブル作成

一般的なDBでは、NULL / NOT NULL で宣言しますが、ClickHouseでは Nullable(元の型名) で宣言します。
次のようになります。

CREATE TABLE test1(
  col1 UInt32,
  col2 Nullable(UInt32),
  col3 String,
  col4 Nullable(String),
  col5 DateTime,
  col6 Nullable(DateTime)
) Engine = MergeTree ORDER BY col1

MergeTreeエンジンの PRIMARY KEY には Nullable なカラムは使用できません。
たとえば上記で ORDER BY col2 はエラーとなります。

NULL値の表現

SQL文の中では、普通に NULL で表記します。

FORMAT句でフォーマットを変更する場合、フォーマットにより表現が異なります。

Pretty、CSV、TSV、XMLの場合は、\N 表記となります。
JSONでは null となります。

コマンドラインでクエリを実行した場合、PrettyCompact フォーマットで表示されるので、結果としては \N 表記になります。

CSV/TSVファイルとしてインポートする場合、NULL値部分には \N を記載する必要があります。
Int 型の場合、空欄にすると、そこには NULL ではなく 0（デフォルト値）が入りますので、注が必要です。

ORDERの順序

ASC/DESC どちらでソートしても、NULL値は常に最後に返されます。

SELECT col1
FROM test2
ORDER BY col1 ASC

┌─col1─┐
│   -1 │
│    1 │
│   \N │
└──────┘

SELECT col1
FROM test2
ORDER BY col1 DESC

┌─col1─┐
│    1 │
│   -1 │
│   \N │
└──────┘

JOINの動作

取り出すカラムの型が Nullable ではない場合、OUTER JOIN した時に、対応するレコードがない場合、NULLではなく型のデフォルト値が返ります。
一般的なDBのように、NULL値を得たい場合は、join_use_nulls フラグをセットしてクエリを発行します。

DESCRIBE TABLE test_a

┌─name─┬─type────────────┬─default_type─┬─default_expression─┐
│ id   │ String          │              │                    │
│ data │ Nullable(Int32) │              │                    │
└──────┴─────────────────┴──────────────┴────────────────────┘

DESCRIBE TABLE test_b

┌─name──┬─type───┬─default_type─┬─default_expression─┐
│ id    │ String │              │                    │
│ data2 │ Int32  │              │                    │
└───────┴────────┴──────────────┴────────────────────┘

SELECT *
FROM test_a

┌─id─┬─data─┐
│ a  │    1 │
│ b  │   \N │
│ c  │    1 │
│ d  │   \N │
└────┴──────┘

SELECT *
FROM test_b

┌─id─┬─data2─┐
│ a  │     1 │
│ b  │     2 │
└────┴───────┘

SELECT
    id,
    data,
    data2
FROM test_a
ANY LEFT JOIN test_b USING (id)

┌─id─┬─data─┬─data2─┐
│ a  │    1 │     1 │
│ b  │   \N │     2 │
│ c  │    1 │     0 │
│ d  │   \N │     0 │
└────┴──────┴───────┘

-- id=c,d のレコードは test_b にありませんが、
-- data2 のカラムが 0 になっています。

:) SET join_use_nulls = 1;

SELECT
    id,
    data,
    data2
FROM test_a
ANY LEFT JOIN test_b USING (id)

┌─id─┬─data─┬─data2─┐
│ a  │    1 │     1 │
│ b  │   \N │     2 │
│ c  │    1 │    \N │
│ d  │   \N │    \N │
└────┴──────┴───────┘

NULL関連の関数

IS NULL / IS NOT NULL

WHERE句などで、IS NULL / IS NOT NULL が利用できます。
使い方は通常のDBと一緒です。

isNull / isNotNull

Nullかどうか0/1で返します。

SELECT isNull(NULL)

┌─isNull(NULL)─┐
│            1 │
└──────────────┘

SELECT isNull(1)

┌─isNull(1)─┐
│         0 │
└───────────┘

SELECT isNotNull(NULL)

┌─isNotNull(NULL)─┐
│               0 │
└─────────────────┘

ifNull / nullIf

ifNull(a,b) は、a が NULL ではない場合 a を返し、a が NULLであれば b を返します。

SELECT ifNull(NULL, 0)

┌─ifNull(NULL, 0)─┐
│               0 │
└─────────────────┘

SELECT ifNull(3, 0)

┌─ifNull(3, 0)─┐
│            3 │
└──────────────┘

nullIf(a,b) は、a==bの場合はNULLを返し、それ以外はaを返します。

SELECT nullIf(1, 1)

┌─nullIf(1, 1)─┐
│           \N │
└──────────────┘

SELECT nullIf(1, 2)

┌─nullIf(1, 2)─┐
│            1 │
└──────────────┘

coalesce

coalesce(a,b,c,...) で、指定されたもののうち、NULLではない最初のものを返します。
すべてNULLの場合や、引数がない場合もNULLを返します。

SELECT coalesce(NULL, NULL, 1)

┌─coalesce(NULL, NULL, 1)─┐
│                       1 │
└─────────────────────────┘

SELECT coalesce(NULL, 1, 2)

┌─coalesce(NULL, 1, 2)─┐
│                    1 │
└──────────────────────┘

assumeNotNull

assumeNotNull(a) で、aがNULLでないと仮定して、Nullableでない型でデータを返します。
NULLが入っていた場合、Int型なら0に、日付型なら 0000-00-00 などの値になります。


SELECT *
FROM test_null

┌─id─┐
│  1 │
│ \N │
└────┘

SELECT id
FROM test_null
FORMAT TabSeparatedWithNamesAndTypes

id
Nullable(UInt32)
1
\N

SELECT assumeNotNull(id)
FROM test_null
FORMAT TabSeparatedWithNamesAndTypes

assumeNotNull(id)
UInt32
1
0