用INNER JOIN替代含OR的WHERE子句：SQL查询优化咨询

我现有一段SQL查询，用于获取具备特定诊断代码（diagnosis codes）或特定结局代码（outcome_codes）的患者ID（pt_id），目前通过WHERE子句指定代码条件实现，但想了解是否可以用INNER JOIN改写该查询：

原查询代码：

SELECT DISTINCT
       pt_id
FROM #df
WHERE flag <> 1
      AND (diagnosis IN
          (
              SELECT Code
              FROM #df_codes
              WHERE code_id = '20'
          )
      OR outcome_code IN
         (
             SELECT Code
             FROM #df_codes
             WHERE code_id = '25'
         ));

这段原查询运行耗时较长，我尝试了如下改写方案，逻辑是让#df与#df_codes在Code匹配diagnosis或Code匹配outcome_code时关联，想咨询两个问题：
1）该方案是否可行合理？
2）能否提升运行速度？

我的改写方案：

SELECT DISTINCT
       pt_id
FROM #df a
JOIN (select * from #df_codes where code_id = '20')b ON a.diagnosis = b.Code OR 
(select * from #df_codes where code_id = '25')c ON a.outcome_code = c.Code
WHERE flag <> 1

解答 #

1）改写方案的可行性

你的改写方案语法不合法，无法直接运行。SQL中JOIN的语法要求每个关联表都必须对应JOIN关键字，你用OR直接连接两个子查询关联条件的写法不符合规范，数据库会抛出语法错误。

推荐两种规范的改写思路：

写法一：单表JOIN结合多条件

SELECT DISTINCT
       a.pt_id
FROM #df a
INNER JOIN #df_codes b
    ON (b.code_id = '20' AND a.diagnosis = b.Code)
    OR (b.code_id = '25' AND a.outcome_code = b.Code)
WHERE a.flag <> 1;

写法二：用UNION拆分两种匹配场景

SELECT DISTINCT pt_id
FROM #df
WHERE flag <> 1
  AND diagnosis IN (SELECT Code FROM #df_codes WHERE code_id = '20')
UNION
SELECT DISTINCT pt_id
FROM #df
WHERE flag <> 1
  AND outcome_code IN (SELECT Code FROM #df_codes WHERE code_id = '25');

这种写法逻辑更清晰，数据库优化器更容易生成高效执行计划。

2）运行速度的提升可能性

是否能提升速度取决于以下核心因素：

• 索引配置：如果#df的diagnosis、outcome_code、flag字段，以及#df_codes的code_id、Code字段都创建了合适的索引，改写后的查询（尤其是UNION写法）大概率比原查询更快。原查询的两个子查询若没有索引，会反复全表扫描#df_codes；改写后的JOIN可以利用索引快速匹配数据。
• 数据量级：若#df数据量极大，原查询的OR条件可能导致数据库无法使用索引，只能全表扫描。而UNION写法可以让两个分支单独利用索引过滤数据，再合并结果，效率更高。
• 数据库优化器逻辑：不同数据库（如SQL Server、MySQL）对IN子查询和JOIN的处理逻辑不同。部分数据库会自动将IN子查询转换为JOIN，但原查询的OR条件可能干扰优化器生成最优计划，改写后的JOIN或UNION写法能帮助优化器选择更高效的执行路径。

另外，无论哪种写法都需要保留DISTINCT，避免返回重复的pt_id。

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