select * from (select 'test' as clo_name union all select 'test1' as clo_name union all select null as clo_name) as a where clo_name <> 'test' ;
select * from (select 'test' as clo_name union all select 'test1' as clo_name union all select null as clo_name) as a where clo_name not in ('test') ;
clo_name字段 取不等于某个值或者not in 某个(些)值的时候, 类似上面这种sql写法,是取不到 clo_name字段为null的记录的,如果需要取到clo_name字段为null的记录,需要做null值处理,如下:
select * from table coalesce(clo_name ,'') <> 'test' ;
select * from table coalesce(clo_name ,'') not in ('test');
或者,将clo_name列的null值处理,提前到数仓中间层处理掉,比如将null值统一处理成’NA‘,后续在使用就不用再考虑这些问题。
所以,我们在日常开发中,要处理这种不等于或not in 的问题的时候需要特别注意,否则很容易跟预想的结果不一致,且不易发现的bug。