Snowflake WHERE子句多OR运算符失效及结果不符问题求解

需求明确 #

需要从TABLE1中排除所有满足以下任一条件的记录：

• DEPTID值存在于TABLE2的DEPTID字段中
• EMPCOUNTRY值存在于TABLE2的EMPCOUNTRY字段中
• EMPZONE值存在于TABLE2的EMPZONE字段中

原查询问题分析 #

最初报错的查询

SELECT * FROM TABLE1 
WHERE TABLE1."DEPTID" NOT IN (SELECT TABLE2."DEPTID" FROM TABLE2)
OR
TABLE1."EMPCOUNTRY" NOT IN (SELECT TABLE2."EMPCOUNTRY" FROM TABLE2)
OR
TABLE1."EMPZONE" NOT IN (SELECT TABLE2."EMPZONE" FROM TABLE2)

报错核心原因：SQL中NOT IN的特性——如果子查询返回的结果包含NULL，整个条件判断会直接返回NULL，最终没有任何记录被返回。

修改后结果不符的查询

SELECT * FROM TABLE1 AS T1
WHERE 
(
  UPPER (T1."DEPTID") NOT IN
    (SELECT UPPER (nvl (T2."DEPTID", '')) FROM TABLE2 AS T2)
OR
 UPPER (T1."EMPZONE") NOT IN
    (SELECT UPPER (nvl (T2."EMPZONE",'')) FROM TABLE2 AS T2)
)

问题点：

1. 逻辑错误：用OR连接条件，会保留只要有一个字段不在TABLE2的记录，和“排除任一匹配记录”的需求完全相反，应该用AND
2. 遗漏字段：未处理EMPCOUNTRY的匹配判断
3. 误匹配风险：用NVL把NULL转为空字符串，可能导致TABLE1中空字符串的字段被错误排除

正确实现方案 #

方案1：使用NOT EXISTS（推荐，不受NULL影响）

NOT EXISTS的逻辑判断不受NULL干扰，且语义更清晰：

SELECT * 
FROM TABLE1 T1
WHERE NOT EXISTS (
    SELECT 1 
    FROM TABLE2 T2
    WHERE T2."DEPTID" = T1."DEPTID"
)
AND NOT EXISTS (
    SELECT 1 
    FROM TABLE2 T2
    WHERE T2."EMPCOUNTRY" = T1."EMPCOUNTRY"
)
AND NOT EXISTS (
    SELECT 1 
    FROM TABLE2 T2
    WHERE T2."EMPZONE" = T1."EMPZONE"
)

如果需要忽略大小写匹配，添加UPPER转换：

SELECT * 
FROM TABLE1 T1
WHERE NOT EXISTS (
    SELECT 1 
    FROM TABLE2 T2
    WHERE UPPER(T2."DEPTID") = UPPER(T1."DEPTID")
)
AND NOT EXISTS (
    SELECT 1 
    FROM TABLE2 T2
    WHERE UPPER(T2."EMPCOUNTRY") = UPPER(T1."EMPCOUNTRY")
)
AND NOT EXISTS (
    SELECT 1 
    FROM TABLE2 T2
    WHERE UPPER(T2."EMPZONE") = UPPER(T1."EMPZONE")
)

方案2：修复NOT IN并处理NULL

如果坚持使用NOT IN，需要先排除子查询中的NULL，同时修正逻辑为AND：

SELECT * 
FROM TABLE1 T1
WHERE UPPER(T1."DEPTID") NOT IN (
    SELECT UPPER(T2."DEPTID") 
    FROM TABLE2 T2
    WHERE T2."DEPTID" IS NOT NULL
)
AND UPPER(T1."EMPCOUNTRY") NOT IN (
    SELECT UPPER(T2."EMPCOUNTRY") 
    FROM TABLE2 T2
    WHERE T2."EMPCOUNTRY" IS NOT NULL
)
AND UPPER(T1."EMPZONE") NOT IN (
    SELECT UPPER(T2."EMPZONE") 
    FROM TABLE2 T2
    WHERE T2."EMPZONE" IS NOT NULL
)

若需要保留TABLE1中字段为NULL的记录，需额外添加判断：

SELECT * 
FROM TABLE1 T1
WHERE (UPPER(T1."DEPTID") NOT IN (
    SELECT UPPER(T2."DEPTID") 
    FROM TABLE2 T2
    WHERE T2."DEPTID" IS NOT NULL
) OR T1."DEPTID" IS NULL)
AND (UPPER(T1."EMPCOUNTRY") NOT IN (
    SELECT UPPER(T2."EMPCOUNTRY") 
    FROM TABLE2 T2
    WHERE T2."EMPCOUNTRY" IS NOT NULL
) OR T1."EMPCOUNTRY" IS NULL)
AND (UPPER(T1."EMPZONE") NOT IN (
    SELECT UPPER(T2."EMPZONE") 
    FROM TABLE2 T2
    WHERE T2."EMPZONE" IS NOT NULL
) OR T1."EMPZONE" IS NULL)

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