基于WHERE子句中CASE表达式的JOIN查询优化方案咨询

原查询通过OR搭配CASE表达式实现不同原因码下的字段关联，在DETAILS_2数据量较大时，这种写法容易导致数据库无法有效利用索引，查询性能会受影响。可以通过拆分查询+UNION ALL的方式优化，让每个分支的关联逻辑更清晰，同时提升执行效率：

SELECT a.Fieldname1, a.Fieldname2, a.fieldname3
FROM #Exceptions a
JOIN [DETAILS_2] b ON a.REASON_CD = 'LOBMKT' AND CONCAT(a.LOB_CD, a.GEO_MARKET) = CONCAT(b.LOB_CD, b.GEO_MARKET)

UNION ALL

SELECT a.Fieldname1, a.Fieldname2, a.fieldname3
FROM #Exceptions a
JOIN [DETAILS_2] b ON a.REASON_CD = 'LOB' AND a.LOB_CD = b.LOB_CD

UNION ALL

SELECT a.Fieldname1, a.Fieldname2, a.fieldname3
FROM #Exceptions a
JOIN [DETAILS_2] b ON a.REASON_CD = 'CAT' AND a.CTRCT_CAT_CD = b.CTRCT_CAT_CD

UNION ALL

SELECT a.Fieldname1, a.Fieldname2, a.fieldname3
FROM #Exceptions a
JOIN [DETAILS_2] b ON a.REASON_CD = 'LOBST' AND CONCAT(a.LOB_CD, a.[derived_GEO_MARKET]) = CONCAT(b.LOB_CD, b.GEO_MARKET)

优化说明 #

• 拆分逻辑：将原查询中每个OR分支拆分为独立子查询，每个子查询只处理一种REASON_CD的关联逻辑，数据库可针对每个分支生成更高效的执行计划。
• 索引利用：若DETAILS_2在LOB_CD、GEO_MARKET、CTRCT_CAT_CD等字段上建有索引，拆分后的查询能直接利用这些索引，避免原写法中OR导致的全表扫描。
• 性能提升：使用UNION ALL而非UNION，由于不同REASON_CD的记录不会重复，无需额外去重操作，进一步提升查询速度。

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