Spark SQL中NULL字段使用NOT IN字符串列表结果异常问题

这是SQL标准三值逻辑的既定行为，不是Spark SQL的异常。SQL的判断逻辑存在三种返回结果：真、假、未知（UNKNOWN），任何值与NULL做比较的结果都是UNKNOWN，包括等于、不等于、IN、NOT IN运算。

• somecol NOT IN ('ABC','DEF') 逻辑等价于 somecol != 'ABC' AND somecol != 'DEF'。当somecol为NULL时，两个不等值判断的结果都是UNKNOWN，AND运算只要有一个子项为UNKNOWN，最终结果就是UNKNOWN。而WHERE子句仅保留判断结果为「真」的行，结果为假、UNKNOWN的行都会被过滤，因此NULL值的行不会被返回。
• 改写为NOT(somecol IN ('ABC','DEF')) 逻辑不生效的原因同理：somecol为NULL时，somecol IN ('ABC','DEF')的结果是UNKNOWN，NOT UNKNOWN的运算结果仍然是UNKNOWN，照样会被WHERE过滤。

根据业务对NULL值的预期返回规则，选择对应写法即可：

• 期望NULL值的行也被返回：在判断条件中显式补充NULL判断

SELECT * FROM mytab WHERE somecol NOT IN ('ABC','DEF') OR somecol IS NULL

• 业务逻辑中NULL等价于空字符串（或其他默认值）：用函数提前转换NULL值再做判断，无需额外写IS NULL条件

-- 将NULL转为空字符串后再做NOT IN判断
SELECT * FROM mytab WHERE COALESCE(somecol, '') NOT IN ('ABC','DEF')

• 期望NULL值的行不返回：现有逻辑完全符合SQL标准，无需修改，只需明确知晓NOT IN会自动过滤字段为NULL的行即可。

注意：判断NULL值必须使用IS NULL/IS NOT NULL语法，somecol = NULL、somecol != NULL这类写法的返回结果永远是UNKNOWN，无法达到NULL判断的效果。

内容的提问来源于stack exchange，提问作者aaa90210