Snowflake子查询不支持错误：如何改写带产品标记的嵌套查询？

你遇到的unsupported sub query type错误，是因为Snowflake对这种多层嵌套的IN子查询格式支持有限。同时原代码里还有一处拼写错误：trial_product应该是trial_products。

下面用**嵌套子查询（派生表）**的方式改写，完全实现你需要的CASE WHEN逻辑：

改写后的查询代码 #

SELECT 
    main.*,
    CASE 
        WHEN trial_exists = TRUE AND discount_exists = FALSE THEN 'Indirect'
        WHEN trial_exists = TRUE AND discount_exists = TRUE THEN 'Direct'
        ELSE NULL 
    END AS DirectIndirectFlag
FROM (
    -- 嵌套子查询：先关联所有表，标记产品是否存在于两个目标表
    SELECT 
        a.*,
        b.address,
        -- 标记是否在trial_products中存在
        CASE WHEN tp.product IS NOT NULL THEN TRUE ELSE FALSE END AS trial_exists,
        -- 标记是否在discount_table中存在
        CASE WHEN dt.product IS NOT NULL THEN TRUE ELSE FALSE END AS discount_exists
    FROM salesTable a
    LEFT JOIN all_products b 
        ON a.product = b.product
    -- 左连接预聚合的trial_products派生表
    LEFT JOIN (SELECT DISTINCT product FROM trial_products) tp
        ON a.product = tp.product
    -- 左连接预聚合的discount_table派生表
    LEFT JOIN (SELECT DISTINCT product FROM discount_table) dt
        ON a.product = dt.product
) main

逻辑说明 #

1. 先通过嵌套子查询（外层的main表），将salesTable与两个预聚合了DISTINCT product的派生表左连接，用IS NOT NULL判断产品是否存在于目标表，生成两个布尔标记字段。
2. 外层查询直接用这两个标记字段写CASE WHEN逻辑，完全规避了Snowflake不支持的嵌套子查询格式，同时逻辑和原需求完全匹配。

如果你希望更简洁，也可以直接在主查询里关联派生表后写CASE WHEN，省去外层嵌套：

SELECT 
    a.*, 
    b.address,
    CASE 
        WHEN tp.product IS NOT NULL AND dt.product IS NULL THEN 'Indirect'
        WHEN tp.product IS NOT NULL AND dt.product IS NOT NULL THEN 'Direct'
        ELSE NULL 
    END AS DirectIndirectFlag
FROM salesTable a
LEFT JOIN all_products b 
    ON a.product = b.product
LEFT JOIN (SELECT DISTINCT product FROM trial_products) tp
    ON a.product = tp.product
LEFT JOIN (SELECT DISTINCT product FROM discount_table) dt
    ON a.product = dt.product

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