sql - ClickHouse:0 是 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

如何理解0是0还是0是Null？

Answer 1

问题在于如何处理外连接。根据文档，这使用设置 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.

换句话说，默认方法是不符合 SQL 的。就个人而言，我认为这样的默认值没有用。我猜 Clickhouse 对 NULL 值有强烈的反感。

因此，您可以改为检查 a.id = b.id 以查看是否存在匹配项。