CROSS APPLY中外层表可见性问题：递归查询为何抛ORA-00904？

问题本质 #

这不是你对CROSS APPLY逻辑的误解，而是Oracle对CONNECT BY递归查询的作用域限制导致的——递归子查询的作用域无法直接访问CROSS APPLY外层的表别名（比如t1）。

具体原因 #

1. 递归查询的独立执行特性 #

Oracle的CONNECT BY递归查询（包括它所在的内联视图）会被解析器当作一个独立的预编译单元。在预编译阶段，递归查询无法识别到CROSS APPLY外层的逐行传递变量（比如t1.col_id），这就导致了ORA-00904标识符无效的错误。

而你第一个正常的CROSS APPLY示例里，子查询是普通的关联逻辑，属于关联子查询，Oracle会处理逐行的关联引用，所以可以正常访问外层t1的行值。

2. SELECT列表标量子查询的特殊处理 #

当把递归子查询放到SELECT列表中时，它属于标量子查询，Oracle对这类子查询的作用域规则不同：标量子查询会和外层查询紧密绑定，允许直接引用外层的行值，哪怕内部包含递归逻辑，这就是第三个示例能正常运行的原因。

替代实现方案（不用JOIN的话） #

如果想继续用CROSS APPLY完成需求，可以把外层行值的过滤提前到递归子查询的FROM子句中，避免在START WITH里引用外层变量，示例如下：

WITH table_1 AS (
  SELECT 1 col_id FROM dual UNION ALL
  SELECT 2 col_id FROM dual UNION ALL
  SELECT 4 col_id FROM dual
),
table_parents AS (
  SELECT 1 col_id, 3 parent_id, 'manager' parent_type FROM dual UNION ALL
  SELECT 2 col_id, 3 parent_id, 'manager' parent_type FROM dual UNION ALL
  SELECT 3 col_id, 4 parent_id, 'manager' parent_type FROM dual
)
SELECT t1.col_id
     , uptimate_parent.parent_id
FROM table_1 t1
CROSS APPLY (
  SELECT parent_id
  FROM (
    SELECT p.col_id, p.parent_id
    FROM table_parents p
    WHERE p.parent_type = 'manager'
      AND p.col_id = t1.col_id -- 提前过滤当前t1对应的行，再执行递归
    CONNECT BY NOCYCLE PRIOR p.parent_id = p.col_id
  ) pars
  WHERE connect_by_isleaf = 1
) uptimate_parent;

总结 #

• CROSS APPLY本身支持关联外层行值，但CONNECT BY递归查询的作用域无法跨越到CROSS APPLY外层，因为递归查询是独立预编译的执行单元。
• SELECT列表中的标量子查询不受此限制，因为它的作用域与外层查询直接绑定。

内容的提问来源于stack exchange，提问作者Виталий Яндулов