SQL中使用CASE WHEN时如何将关联子查询转换为JOIN

问题背景 #

原CASE语句逻辑：

CASE WHEN T3.ID_NUMBER IN (20216) THEN 'Retired'

想要新增子查询逻辑缩小判断范围，改写后语句如下：

CASE WHEN T3.ID_NUMBER IN (20216) 
AND T1.DEPARTMENT NOT IN (
    SELECT T1.DEPARTMENT 
    WHERE
        (T.JOB_TYPE IN ('TESTING') AND T2.GROUP IN ('A', 'C', 'D')) 
        OR 
        (T.JOB_TYPE IN ('PRODUCTION') AND T2.GROUP IN ('G', 'E'))
)
THEN 'Retired'

执行时触发报错：不支持此类关联查询形式，请考虑重写。

解决方案：将子查询转换为JOIN #

核心思路是把需要排除的部门提前通过JOIN筛选出来，再在CASE语句中判断是否匹配。

步骤1：提取排除部门的逻辑 #

先定义需要排除的部门集合（满足以下任一条件的部门）：

• T表的JOB_TYPE为TESTING且T2表的GROUP为A/C/D
• T表的JOB_TYPE为PRODUCTION且T2表的GROUP为G/E

步骤2：用LEFT JOIN替代NOT IN子查询 #

以下是两种可行写法：

写法1：使用CTE（公共表表达式）

WITH excluded_departments AS (
    SELECT DISTINCT T1.DEPARTMENT
    FROM T
    -- 补充T与T2、T1之间的关联条件，示例：
    -- JOIN T2 ON T.T2_ID = T2.ID
    -- JOIN T1 ON T.T1_ID = T1.ID
    WHERE 
        (T.JOB_TYPE = 'TESTING' AND T2."GROUP" IN ('A', 'C', 'D'))
        OR 
        (T.JOB_TYPE = 'PRODUCTION' AND T2."GROUP" IN ('G', 'E'))
)
SELECT 
    -- 其他需要查询的字段
    CASE 
        WHEN T3.ID_NUMBER IN (20216) 
             AND ed.DEPARTMENT IS NULL 
        THEN 'Retired'
        -- 按需添加ELSE分支
        ELSE NULL
    END AS STATUS
FROM 
    -- 原查询中的表，补充关联条件，示例：
    -- T1 JOIN T3 ON T1.ID = T3.T1_ID
    T1
JOIN T3 ON T1.ID = T3.T1_ID
LEFT JOIN excluded_departments ed ON T1.DEPARTMENT = ed.DEPARTMENT
-- 原查询的其他WHERE条件

写法2：使用内嵌子查询

SELECT 
    -- 其他需要查询的字段
    CASE 
        WHEN T3.ID_NUMBER IN (20216) 
             AND ed.DEPARTMENT IS NULL 
        THEN 'Retired'
        ELSE NULL
    END AS STATUS
FROM 
    T1
JOIN T3 ON T1.ID = T3.T1_ID
LEFT JOIN (
    SELECT DISTINCT T1.DEPARTMENT
    FROM T
    -- 补充表关联条件
    -- JOIN T2 ON T.T2_ID = T2.ID
    -- JOIN T1 ON T.T1_ID = T1.ID
    WHERE 
        (T.JOB_TYPE = 'TESTING' AND T2."GROUP" IN ('A', 'C', 'D'))
        OR 
        (T.JOB_TYPE = 'PRODUCTION' AND T2."GROUP" IN ('G', 'E'))
) ed ON T1.DEPARTMENT = ed.DEPARTMENT
-- 原查询的其他WHERE条件

关键注意事项 #

• GROUP是SQL关键字，需要用双引号（"GROUP"）或方括号（[GROUP]）转义，具体格式根据使用的数据库调整
• 子查询中必须加DISTINCT，避免同一部门重复出现导致JOIN后数据行数翻倍
• 务必补充原问题中缺失的表关联条件（比如T与T2、T1之间的关联字段），否则查询无法正常执行

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