将Pandas IS IN逻辑转换为支持多OR条件的Oracle SQL查询

原SQL逻辑回顾 #

原代码用于判断testschema.testtable1的tn_id是否存在于cu.customer的customer_id中，返回"Yes"/"No"，修正语法错误后的原代码如下：

SELECT
    testtable1.id,
    CASE
        WHEN (testtable1.tn_id IN (
            SELECT b."customer_id"
            FROM cu.customer b
        ))
        THEN 'Yes'
        ELSE 'No'
    END AS BLACKLIST
FROM
    testschema.testtable1

扩展多OR条件的可行方案 #

方案1：直接用OR连接多个IN子句 #

如果需要判断目标字段满足任意一个独立规则，直接在CASE的WHEN分支里用OR串联多个IN条件，逻辑直观易懂：

SELECT
    testtable1.id,
    CASE
        WHEN (testtable1.tn_id IN (SELECT b."customer_id" FROM cu.customer b)
             OR testtable1.tn_id IN (SELECT c."blocked_id" FROM cu.blocked_list c)
             OR testtable1.phone IN (SELECT d."restricted_phone" FROM cu.restricted d))
        THEN 'Yes'
        ELSE 'No'
    END AS BLACKLIST
FROM
    testschema.testtable1

方案2：合并子查询为单个集合 #

若多个条件针对同一个字段，可将所有匹配值合并到一个子查询中，用UNION ALL（需去重则用UNION）减少OR分支数量：

SELECT
    testtable1.id,
    CASE
        WHEN testtable1.tn_id IN (
            SELECT b."customer_id" FROM cu.customer b
            UNION ALL
            SELECT c."blocked_id" FROM cu.blocked_list c
            UNION ALL
            SELECT e."ban_id" FROM cu.banned e
        )
        THEN 'Yes'
        ELSE 'No'
    END AS BLACKLIST
FROM
    testschema.testtable1

方案3：用EXISTS替代IN（大数据量场景优先） #

当涉及大数据量表时，EXISTS性能通常优于IN，因为它找到匹配项后会立即终止查询，可通过OR串联多个EXISTS子句：

SELECT
    testtable1.id,
    CASE
        WHEN EXISTS (SELECT 1 FROM cu.customer b WHERE b."customer_id" = testtable1.tn_id)
             OR EXISTS (SELECT 1 FROM cu.blocked_list c WHERE c."blocked_id" = testtable1.tn_id)
             OR EXISTS (SELECT 1 FROM cu.restricted d WHERE d."restricted_phone" = testtable1.phone)
        THEN 'Yes'
        ELSE 'No'
    END AS BLACKLIST
FROM
    testschema.testtable1

方案4：LEFT JOIN + 非空判断 #

若需要同时关联多个表获取额外信息，可采用LEFT JOIN方式，通过判断关联结果是否为空来返回对应值：

SELECT
    t1.id,
    CASE
        WHEN c.customer_id IS NOT NULL
             OR bl.blocked_id IS NOT NULL
             OR r.restricted_phone IS NOT NULL
        THEN 'Yes'
        ELSE 'No'
    END AS BLACKLIST
FROM
    testschema.testtable1 t1
LEFT JOIN cu.customer c ON c."customer_id" = t1.tn_id
LEFT JOIN cu.blocked_list bl ON bl.blocked_id = t1.tn_id
LEFT JOIN cu.restricted r ON r.restricted_phone = t1.phone

注：若主表行匹配多个关联表导致重复，可添加DISTINCT去重。

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