BigQuery中NOT IN子查询突然失效问题求助

最核心的原因：子查询返回结果包含NULL值 #

SQL中NOT IN的逻辑是所有条件必须同时成立，只要子查询里存在一个NULL值，整个表达式的结果会变成UNKNOWN，而WHERE子句只会保留结果为TRUE的行，最终导致返回空集。

举个简单例子：

-- 假设子查询返回 ('A', 'B', NULL)
SELECT * FROM NEW_DATA_SOURCE
WHERE CONCAT(ContractID, OtherField) NOT IN (SELECT CONCAT(ContractID, OtherField) FROM OLD_DATA_SOURCE);

等价于：

SELECT * FROM NEW_DATA_SOURCE
WHERE 
  CONCAT(...) != 'A' 
  AND CONCAT(...) != 'B' 
  AND CONCAT(...) != NULL; -- 这部分结果是UNKNOWN

因为SQL中任何值与NULL比较的结果都是UNKNOWN，整个AND表达式的结果也会变成UNKNOWN，最终所有行都被过滤，返回空。

而LEFT OUTER JOIN不受这个限制：

SELECT n.* 
FROM NEW_DATA_SOURCE n
LEFT JOIN OLD_DATA_SOURCE o 
  ON n.CONCAT_KEY = o.CONCAT_KEY
WHERE o.CONCAT_KEY IS NULL;

即使o.CONCAT_KEY存在NULL，只要n的键在o中没有匹配（包括o的NULL不会匹配任何n的键），就能正常返回符合条件的行。

次要原因：CONCAT生成的键存在一致性问题 #

如果CONCAT拼接的字段存在以下情况，也会导致NOT IN失效：

• 隐式类型转换：比如一边拼接的是数字类型字段，另一边是字符串类型，数据库自动转换后生成的键值不一致（例如CONCAT(123, 'ABC')和CONCAT('123', 'ABC')在某些数据库中可能被视为不同值）。
• 字符格式差异：比如字段类型为CHAR时会自动补空格，而VARCHAR不会，导致拼接后的键值看起来相同但实际有尾部空格；或者字符编码不一致（如UTF-8和GBK）导致字节序列不同。
• 拼接字段含NULL：如果OLD_DATA_SOURCE中用于拼接的某个字段是NULL，CONCAT结果会直接变成NULL，这又会触发上面提到的NOT IN遇NULL失效的问题。

边缘原因：数据库执行计划异常 #

如果数据库的统计信息过时，或者子查询数据量突然增大，优化器可能选择了错误的执行计划，比如错误地将NOT IN转换为等价的NOT EXISTS逻辑，或者哈希匹配时出现异常，导致结果为空。这种情况可以通过更新统计信息或强制指定执行计划来验证。

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