clickhouse 踩过的坑-526互联

【1】clickhouse left join 时 where条件判空无效

DROP TABLE if exists tablename1;
create table tablename1 engine = MergeTree order by column1
as
select t.* from tablename2 t
left join tablename3 t1 on t.column1=t1.column1
where t1.column is null;

默认管理时右表字段为'' 而不是null，因此条件where t1.column is null将剔除所有记录
正确写法：

DROP TABLE if exists tablename1;
create table tablename1 engine = MergeTree order by column1
as
select t.* from tablename2 t
left join tablename3 t1 on t.column1=t1.column1
where t1.column='';