关联子查询异常表现探究：为何注释字段后查询结果不同？

现有两个CTE视图sd和t，执行如下SQL语句：

with sd as 
(
    select 1 id, 'abc' col from dual
    union
    select 2 id, 'xyz' col from dual
    union
    select 3 id, 'pqr' col from dual
),
t as 
(
    select 1 id, '123' col2 from dual
    union
    select 2 id, '233' col2 from dual
    union
    select 4 id, '456' col2 from dual
)
SELECT 
    id,
    col2,
    (SELECT 'ok' FROM dual WHERE SD_EXIST = 'Y') CHECK_CON
    --,SD_EXIST
FROM 
    (SELECT
         id,
         col2,
         CASE 
             WHEN EXISTS (SELECT 1 FROM sd WHERE sd.id = t.id)
                 THEN 'Y' 
                 ELSE NULL 
         END AS SD_EXIST
     FROM
         t) ;

内联视图预期返回结果：

ID COL2 SD_EXIST
----------------
1   123   Y
2   233   Y
4   456   -

实际执行出现异常：

• 当SELECT列表包含SD_EXIST时，id=4的CHECK_CON为NULL（符合预期）；
• 当注释掉SD_EXIST后，id=4的CHECK_CON变为'ok'。

请解释该现象的逻辑原理及产生原因。

这是Oracle数据库的查询优化器冗余列消除逻辑导致的：

1. 保留SD_EXIST列时：
   因为外部查询直接引用了内联视图的SD_EXIST列，优化器必须保留CASE WHEN EXISTS(...)的计算逻辑。此时id=4的SD_EXIST值为NULL，外部子查询(SELECT 'ok' FROM dual WHERE SD_EXIST = 'Y')的条件不成立，返回NULL，符合预期。

2. 注释掉SD_EXIST列后：
   内联视图中的SD_EXIST列不再被外部查询直接引用，Oracle优化器会判定该列为冗余，直接从执行计划中移除生成SD_EXIST的CASE WHEN EXISTS(...)计算逻辑。此时外部子查询里的SD_EXIST = 'Y'失去了实际的计算依据，优化器会将这个条件视为永真（相当于没有有效条件约束），因此子查询返回'ok'。

本质是优化器会自动移除未被引用的冗余列及相关计算逻辑，导致依赖该列的子查询条件失效，出现不符合预期的结果。

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