ClickHouse: where 0 is Null?

Question

create database test

create table test.A (id UInt8, data String) engine MergeTree() order by (id)

create table test.B (id UInt8, data String) engine MergeTree() order by (id)

insert into test.A values (0,'a'),(1,'b'),(2,'c')

insert into test.B values (1,'x'),(2,'y'),(3,'z')

select *, isNull(a.id), isNull(b.id) from test.A a full join test.B b on a.id = b.id

How to understand if 0 is 0 or 0 is Null?

Answer 1

The issue is how the outer join is processed. According to the documentation, this uses a setting join_use_nulls:

Sets the type of JOIN behaviour. When merging tables, empty cells may appear. ClickHouse fills them differently based on this setting.

Possible values:

0 — The empty cells are filled with the default value of the corresponding field type. 1 — JOIN behaves the same way as in standard SQL. The type of the corresponding field is converted to Nullable, and empty cells are filled with NULL.

Default value: 0.

In other words, the default approach is to be non-SQL compliant. Personally, I see no use for such a default. I guess there is a strong aversion to NULL values at Clickhouse.

So, you can instead check a.id = b.id to see if there is a match.