Google BigQuery使用NOT IN时匹配NULL值问题及解决方法

1. 该现象的底层原因 #

SQL遵循三值逻辑规则，表达式的运算结果除了TRUE和FALSE外，还存在UNKNOWN状态。
当some_column的值为NULL时，NULL NOT IN ('value_which_does_not_exist1', 'value_which_does_not_exist2')这类涉及NULL的等值比较运算，结果会返回UNKNOWN而非预期的TRUE。
而CASE表达式仅会在WHEN后的条件严格等于TRUE时进入对应分支，其余FALSE和UNKNOWN的情况都会触发ELSE分支，因此所有some_column为NULL的行都会被判定为match = false，就出现了你观测到的结果。

2. 无需额外过滤NULL的替代方案 #

你可以直接停用NOT IN语法，改用兼容性更强、逻辑更稳定的NOT EXISTS方案，完全规避NULL值处理问题，示例改写如下：

SELECT CASE WHEN NOT EXISTS (
            SELECT 1 
            FROM (VALUES ('value_which_does_not_exist1'), ('value_which_does_not_exist2')) AS allowed_values(val)
            WHERE allowed_values.val = some_column
        ) THEN true
        ELSE false
        END match,
        COUNT(some_column) n_events
FROM 'some_table'
GROUP BY match

NOT EXISTS采用二值逻辑判断，只要子查询无匹配行就返回TRUE，哪怕比较的字段为NULL也不会出现UNKNOWN的异常状态，完全符合预期逻辑。

如果你使用的是支持IS DISTINCT FROM语法的现代数据库（PostgreSQL、SQL Server 2022+、MySQL 8.0.13+），也可以用更简洁的写法：

SELECT CASE WHEN some_column IS DISTINCT FROM ALL (ARRAY['value_which_does_not_exist1', 'value_which_does_not_exist2'])
        THEN true
        ELSE false
        END match,
        COUNT(some_column) n_events
FROM 'some_table'
GROUP BY match

该语法专门处理含NULL的等值比较，默认NULL与所有非NULL值不相等，同样不需要额外处理NULL值。

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