BigQuery中NOT IN查询结果异常问题求助

这是因为**dataset.second_table的key列存在NULL值**，这是NOT IN子查询的典型逻辑陷阱。

核心逻辑解释 #

SQL中，当NOT IN的子查询结果包含NULL时，整个条件的判断会失效：

• 假设子查询返回[val1, val2, NULL]，那么key NOT IN (...)等价于key != val1 AND key != val2 AND key != NULL
• 而SQL中任何与NULL的比较结果都是UNKNOWN，整个逻辑表达式最终会返回UNKNOWN，不会被判定为TRUE，因此没有任何行能满足该条件，最终返回0条结果。

验证方式 #

你可以执行以下语句确认second_table是否存在NULL的key：

SELECT COUNT(*) FROM dataset.second_table WHERE key IS NULL

如果返回值大于0，就验证了这个问题。

修复方案 #

有两种可靠的修正方式：

1. 在子查询中排除NULL值：

SELECT DISTINCT key FROM dataset.first_table 
WHERE key NOT IN (SELECT key FROM dataset.second_table WHERE key IS NOT NULL)

2. 改用NOT EXISTS（推荐写法，天然避免NULL陷阱）：

SELECT DISTINCT key FROM dataset.first_table t1
WHERE NOT EXISTS (SELECT 1 FROM dataset.second_table t2 WHERE t2.key = t1.key)

以上两种写法都会得到和左连接查询一致的结果（2,395,612条）。

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