大数据踩过的坑——Hive where 条件中 not in 或者 in的隐藏信息

最近查询数据的时候遇到了一个问题，百思不得其解，具体现象如下：

select count(1) from idm.idm_table1_s_d where dt = '2020-03-18'
结果为：2053683514

select count(1) from idm.idm_table1_s_d where dt = '2020-03-18' and col_1 in ('1978004','1978003')
结果为：2053683514

select count(1) from idm.idm_table1_s_d where dt = '2020-03-18' and col_1 not in ('1978004','1978003')
结果为：192030210

你会发现in 的结果加上 not in 的结果 并不等于总数。后经过多方查询及请教，才找到问题的根源：

我的数据源表的col_1是有空值的，而hive的where条件中使用的not in 或者in时，隐藏了 is not null 的条件。

select count(1) from idm.idm_table1_s_d where dt = '2020-03-18' and col_1 is null

结果为：1835016104

这样你会发现总数=not in 的数量+in的数量+is null的数量。

即where col_1 in ('1978004','1978003') 其实等效于 where col_1 in ('1978004','1978003')  and col_1 is not null