Snowflake中NOT IN子查询报错，求CASE语句正确实现方式

问题背景 #

原代码使用IN子查询时正常，但NOT IN触发Snowflake错误：

'Unsupported subquery type cannot be evaluated'

原代码：

case when lower(last_name_part2) not in (select lower(last_name) from scratch.white_surnames) then 1 else 0 end as nonwhite_names

替代方案 #

方案1：LEFT JOIN + IS NULL（推荐，性能稳定） #

通过左连接匹配姓氏表，未匹配到的即为目标值，同时用DISTINCT避免重复匹配导致的结果行数异常：

SELECT
    原表字段列表,
    CASE WHEN s.last_name IS NULL THEN 1 ELSE 0 END AS nonwhite_names
FROM 你的业务表 t
LEFT JOIN (
    SELECT DISTINCT lower(last_name) AS last_name 
    FROM scratch.white_surnames
) s ON lower(t.last_name_part2) = s.last_name

方案2：NOT EXISTS子查询（逻辑直观） #

Snowflake对NOT EXISTS的支持比NOT IN更友好，同时避免NOT IN遇到NULL值时的逻辑异常（若子查询存在NULL，NOT IN会返回UNKNOWN，导致CASE条件不生效）：

SELECT
    *,
    CASE WHEN NOT EXISTS (
        SELECT 1 
        FROM scratch.white_surnames s
        WHERE lower(s.last_name) = lower(last_name_part2)
    ) THEN 1 ELSE 0 END AS nonwhite_names
FROM 你的业务表

方案3：ARRAY_CONTAINS（适合小数据集） #

将姓氏表转为数组后，用数组包含判断实现逻辑，仅推荐姓氏数据量较小的场景：

WITH surname_array AS (
    SELECT ARRAY_AGG(DISTINCT lower(last_name)) AS surname_list
    FROM scratch.white_surnames
)
SELECT
    t.*,
    CASE WHEN NOT ARRAY_CONTAINS(s.surname_list, lower(t.last_name_part2)) THEN 1 ELSE 0 END AS nonwhite_names
FROM 你的业务表 t, surname_array s

报错原因说明 #

Snowflake的查询优化器对CASE语句内的NOT IN相关子查询支持有限，且NOT IN本身存在NULL值隐患——若scratch.white_surnames的last_name字段包含NULL，NOT IN会返回UNKNOWN，导致CASE分支逻辑不符合预期。上述替代方案既解决了语法支持问题，也规避了逻辑漏洞。

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