带子查询的CASE是否仅在不匹配时执行？及执行计划解析

问题背景 #

我需要实现这样的逻辑：从关联表中获取某列数据，关联失败时就用子查询兜底，对应的SQL语句如下：

SELECT 
    m.a, m.b,
    CASE 
        WHEN o.c IS NOT NULL 
            THEN o.c
            ELSE (SELECT TOP 1 c FROM other_table 
                  WHERE <predicate over o.* and m.*> 
                  ORDER BY ...)
    END as c
FROM 
    main_table m
LEFT JOIN 
    other_table o ON m.key = o.key

这个语句功能正常，但我有两个核心疑问：

• 性能收益是否存在：大部分情况关联匹配就能拿到数据，只有少数行需要执行耗时的TOP 1子查询，优化器能识别这个逻辑吗？还是会对每一行都执行子查询？
• 执行计划解读困惑：查看执行计划时，顶部分支是快速的LEFT JOIN，底部分支是TOP 1子查询（本质是LEFT JOIN变体），高亮节点是CASE运算符。查询返回总行数为3127（即main_table的行数），LEFT JOIN未匹配的行数是328，但计划里两个常量扫描叶子节点起始为289行，合并成578行后又回到289行，完全无法理解。另外看起来最后一个嵌套循环会无条件调用底部分支，是否存在“条件嵌套循环”？要不要改写查询？

解答 #

1. 优化器对CASE分支的处理逻辑 #

主流SQL优化器（如SQL Server、PostgreSQL）不会对每一行都执行子查询，它能识别CASE的短路逻辑——只有当o.c IS NULL时，才会触发ELSE分支的子查询。但要注意：如果子查询的谓词引用了外部查询的列（比如o.*或m.*），优化器会将其转换为相关子查询，但依然遵循CASE的短路规则，仅在需要时执行。

少数例外情况：如果优化器判断子查询执行成本极低，或者统计信息不准确导致误判匹配行数，可能会提前执行所有子查询再过滤，但这种场景非常少见。

2. 执行计划的关键解读 #

你看到的“最后一个嵌套循环无条件调用底部分支”，其实是优化器延迟执行策略的表现：

• 顶部分支的LEFT JOIN优先执行，拿到所有匹配的o.c数据；
• 底部分支的TOP 1子查询执行计划，是优化器为ELSE分支预生成的逻辑，但并不会真的对所有行执行——只有当CASE判断o.c IS NULL时，才会触发这部分逻辑的执行；
• 关于行数的困惑：执行计划里的行数是优化器基于统计信息的预估行数，不是实际执行行数。289、578这些数字是优化器对“需要走ELSE分支的行数”的预估，和实际328未匹配行数有差异，大概率是统计信息过时，或是谓词复杂度导致预估不准，属于正常现象。

3. 是否需要改写查询？ #

如果当前查询性能满足需求，不需要改写。但如果发现ELSE分支的子查询确实被频繁执行（可通过实际执行计划的「实际行数」确认），可以考虑以下两种改写方式，让逻辑更清晰，也帮助优化器生成更高效的执行计划：

方式一：用LEFT JOIN + COALESCE替代CASE

把ELSE分支的子查询转换为OUTER APPLY关联，再用COALESCE取第一个非空值：

SELECT 
    m.a, m.b,
    COALESCE(o.c, o_fallback.c) as c
FROM 
    main_table m
LEFT JOIN 
    other_table o ON m.key = o.key
OUTER APPLY (
    SELECT TOP 1 c 
    FROM other_table 
    WHERE <predicate over o.* and m.*> 
    ORDER BY ...
) o_fallback

这种写法把分支逻辑显式转换为关联操作，优化器更容易识别，你也能直观看到哪些行走了兜底分支。

方式二：拆分查询为UNION ALL

如果未匹配行数占比极低，可以拆分为两个独立查询，先取匹配行，再取未匹配行并执行子查询，最后合并结果：

-- 取关联匹配的行
SELECT m.a, m.b, o.c
FROM main_table m
JOIN other_table o ON m.key = o.key

UNION ALL

-- 取未匹配的行，执行兜底子查询
SELECT m.a, m.b, (
    SELECT TOP 1 c 
    FROM other_table 
    WHERE <predicate over m.*>  -- 此处o.*为空，可调整谓词简化逻辑
    ORDER BY ...
) as c
FROM main_table m
LEFT JOIN other_table o ON m.key = o.key
WHERE o.c IS NULL

这种写法能确保只有未匹配的行才会执行子查询，避免优化器误判，适合未匹配行数占比极小的场景。

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