特定条件下关联Table A与Table B的SQL方案优化咨询

需求背景 #

• Table A 包含 instance_id（注：原SQL中写为issue_id，推测为笔误）和 type 字段，type 仅取值为 T 或 F
• Table B 包含 user_id 和 entitlement 字段，entitlement 仅取值为1、2、3
• 关联规则：
  • 当A的type为T时，关联B中entitlement为1、2、3的记录
  • 当A的type为F时，仅关联B中entitlement为1、2的记录

原实现方案 #

select A.issue_id, A.type from A
join
(select B.user_id, entitlement from B where entitlement in (1,2,3)) tB
where A.type = 'T'
union all
select A.issue_id, A.type from A
join
(select B.user_id, entitlement from B where entitlement in (1,2)) tB
where A.type = 'F'

更优实现方案 #

方案一：合并关联条件到JOIN子句 #

将两种场景的关联逻辑直接写到JOIN的ON条件中，仅需一次表关联即可完成需求，避免拆分查询后UNION ALL，代码更简洁且性能更优：

select A.issue_id, A.type, B.user_id, B.entitlement
from A
join B 
  on (A.type = 'T' and B.entitlement in (1,2,3))
  or (A.type = 'F' and B.entitlement in (1,2))

方案二：利用数值特性简化条件 #

由于entitlement的取值仅为1、2、3，可以通过CASE表达式动态限定允许的最大值，进一步简化代码：

select A.issue_id, A.type, B.user_id, B.entitlement
from A
join B 
  on B.entitlement <= case when A.type = 'T' then 3 else 2 end

两种优化方案均只需扫描一次A表和B表，相比原方案的两次扫描+合并操作，在数据量较大时能显著提升执行效率，同时代码逻辑更清晰，便于后期维护。

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