如何将CASE表达式内的SELECT语句移出以优化SQL查询性能？

首先注意：你的原查询里，a.id本身就来自emp_data，所以a.id in (select id from emp_data)的结果永远为真，valid_id会一直是1。如果这是笔误（比如实际要检查a.id是否存在于另一张表），下面是通用的优化方案。

方法1：用LEFT JOIN替换子查询判断存在性 #

把CASE里的子查询存在性判断改成LEFT JOIN操作，数据库能更好地利用索引和执行计划优化，避免逐行执行子查询。

修改后SQL（假设实际要检查的是其他表的id存在性）： #

with emp_data as(
    select id, *
    from table_a
),
-- 预存需要检查的id集合（如果需要过滤可以在这里加条件）
target_ids as(
    select distinct id from emp_data -- 这里替换成实际要检查的表
),
final_data as(
    select 
        a.*,
        b.*, -- 注意避免id字段重复，可显式指定字段代替*
        case when t.id is not null then 1 else 0 end as valid_id
    from emp_data a
    left join corp_data b on a.id = b.id
    left join target_ids t on a.id = t.id
)

如果原需求确实是检查当前id在emp_data中： #

因为a本身来自emp_data，id必然存在，直接赋值即可：

with emp_data as(
    select id, *
    from table_a
),
final_data as(
    select 
        a.*,
        b.*,
        1 as valid_id
    from emp_data a
    left join corp_data b on a.id = b.id
)

方法2：用EXISTS改写（适合带条件的存在性判断） #

如果子查询带有过滤条件，用EXISTS比IN更高效，且可以避免重复id的问题：

with emp_data as(
    select id, *
    from table_a
),
final_data as(
    select 
        *,
        case when exists(
            select 1 from target_table t 
            where t.id = a.id 
            and t.status = 'active' -- 这里加过滤条件
        ) then 1 else 0 end as valid_id
    from emp_data a
    left join corp_data b on a.id = b.id
)

注：EXISTS在大多数数据库中会被优化为半连接，性能比IN更稳定，但如果过滤条件复杂，还是推荐先预存目标id集合再JOIN。

方法3：窗口函数（适合分组内的存在性判断） #

如果需要基于分组判断id的存在性，用窗口函数可以一次计算完成：

with emp_data as(
    select id, *
    from table_a
),
final_data as(
    select 
        *,
        -- 按id分组，判断组内是否存在符合条件的记录
        max(case when t.status = 'active' then 1 else 0 end) over (partition by a.id) as valid_id
    from emp_data a
    left join corp_data b on a.id = b.id
    left join target_table t on a.id = t.id
)

核心优化思路：把逐行执行的相关子查询，转化为集合级别的预计算或JOIN操作，让数据库引擎能利用索引、并行扫描等优化手段，避免逐行遍历的性能损耗。

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