Oracle层级查询结果集使用IN子句匹配多值的SQL语法问题

使用CONNECT BY PRIOR做层级查询得到结果集后，需要校验该结果集中是否存在任意值属于另一张表的查询结果集，原写法直接将多行列子查询放在IN左侧会触发语法错误，无法实现多值集合的匹配校验。

• 原SQL中WHERE子句左侧的层级查询子查询返回多条cod_nivel_estr_art记录，属于多值集合，无法直接作为IN操作符的左值，运行时会抛出ORA-01427: 单行子查询返回多个行错误
• IN操作符的逻辑是「单个左值是否存在于右值集合中」，如果要判断两个集合是否存在交集，需要调整写法

推荐用EXISTS关键字实现交集判断逻辑，性能比集合计数类写法更优，匹配到符合条件的记录就会终止扫描。
如果你的需求是统计SIC_NEA_CATFRU中匹配层级查询结果的记录数，可以使用如下写法：

SELECT COUNT('X')
INTO V_COUNT 
FROM SIC_NEA_CATFRU s
WHERE EXISTS (
    SELECT 1
    FROM niveles_estr_art n
    WHERE n.cod_nivel_estr_art = s.COD_NIVEL_eSTR_ART
    CONNECT BY PRIOR n.cod_nivel_estr_art_P = n.cod_nivel_estr_art
    START WITH n.cod_nivel_estr_art = V_COD_NIVEL_eSTR_ART
);

如果仅需要判断两个集合是否存在交集，不需要统计匹配条数，可以直接从DUAL表查询：

SELECT COUNT('X')
INTO V_COUNT 
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM niveles_estr_art n
    JOIN SIC_NEA_CATFRU s ON n.cod_nivel_estr_art = s.COD_NIVEL_eSTR_ART
    CONNECT BY PRIOR n.cod_nivel_estr_art_P = n.cod_nivel_estr_art
    START WITH n.cod_nivel_estr_art = V_COD_NIVEL_eSTR_ART
);

如果习惯用集合匹配逻辑，也可以用INTERSECT判断两个查询是否有交集：

SELECT COUNT('X')
INTO V_COUNT
FROM DUAL
WHERE EXISTS (
    SELECT cod_nivel_estr_art
    FROM niveles_estr_art
    CONNECT BY PRIOR cod_nivel_estr_art_P = cod_nivel_estr_art
    START WITH cod_nivel_estr_art = V_COD_NIVEL_eSTR_ART
    INTERSECT
    SELECT COD_NIVEL_eSTR_ART FROM SIC_NEA_CATFRU
);

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