Snowflake中NOT IN替换为NOT EXISTS/左连接后计数不一致问题

核心差异原因：NULL值的处理逻辑不同 #

NOT IN 和 NOT EXISTS 对NULL值的处理规则完全不同，这是导致计数差异的根本原因：

• 当子查询返回的d.city包含NULL值时，employee.city NOT IN (...)的判断逻辑会返回UNKNOWN，这类行会被直接过滤，不会计入最终结果。
• NOT EXISTS 仅判断是否存在匹配的关联记录，只要找不到匹配项，就会保留该行——包括employee.city本身为NULL的情况。

你的NOT IN查询虽然用了distinct，但只要子查询的d.city存在NULL，就会触发这个问题，导致计数偏小。

验证步骤 #

先执行以下查询，确认子查询是否包含NULL的city值：

SELECT COUNT(*)
FROM (
    SELECT DISTINCT d.city 
    FROM department d, allocation a 
    WHERE d.id = a.id
) sub
WHERE sub.city IS NULL;

如果返回结果大于0，就确认了NULL是差异来源。

解决办法 #

根据你的业务需求选择对应的调整方式：

方式1：让NOT EXISTS和原NOT IN逻辑对齐 #

如果你需要保留原NOT IN的过滤逻辑（即排除employee.city为NULL的行），可以在NOT EXISTS查询中额外添加e.city IS NOT NULL的条件：

SELECT COUNT(*)
FROM employee e
WHERE NOT EXISTS (
    SELECT 1
    FROM department d
    JOIN allocation a ON d.id = a.id
    WHERE d.city = e.city
)
AND e.city IS NOT NULL;

方式2：修正原NOT IN的逻辑（如果需要保留NULL行） #

如果业务上应该保留employee.city为NULL的行，那原NOT IN的结果是错误的，NOT EXISTS/左连接的结果才是正确的，直接使用NOT EXISTS的查询即可。

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