BigQuery中WHERE子句NOT IN条件错误过滤NULL记录求助

问题原因 #

SQL中，NULL与任何值进行比较（包括使用IN/NOT IN）都会返回UNKNOWN，而WHERE子句仅会保留判断结果为true的行，因此无论子查询或常量列表中是否存在NULL，原查询里hash_id为NULL的记录都会被过滤。

解决方案 #

1. 针对子查询场景

方法一：使用NOT EXISTS替代NOT IN

NOT EXISTS的判断逻辑与NOT IN不同，当字段为NULL时，它会正确识别为“无匹配项”，从而保留对应记录：

SELECT hash_id 
FROM `table` t
WHERE NOT EXISTS (
    SELECT 1 
    FROM another_table a
    WHERE a.random_ids = t.hash_id
)

方法二：额外添加OR hash_id IS NULL条件

直接将hash_id为NULL的记录明确纳入结果：

SELECT hash_id 
FROM `table`
WHERE
    hash_id NOT IN (SELECT random_ids FROM another_table) 
    OR hash_id IS NULL

2. 针对常量列表场景

方法一：添加OR hash_id IS NULL条件

同样通过显式条件保留NULL记录：

SELECT hash_id 
FROM `table`
WHERE
    hash_id NOT IN ('123-abc', '456-def', '789-ghslkjhkjh')
    OR hash_id IS NULL

方法二：用NOT EXISTS结合UNION ALL构造常量列表

如果不想直接加OR条件，也可以把常量列表转成子查询用NOT EXISTS判断：

SELECT hash_id 
FROM `table` t
WHERE NOT EXISTS (
    SELECT 1 
    FROM (
        SELECT '123-abc' AS random_ids UNION ALL
        SELECT '456-def' UNION ALL
        SELECT '789-ghslkjhkjh'
    ) a
    WHERE a.random_ids = t.hash_id
)

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