Oracle SQL带日期过滤的JOIN查询优化方案咨询

需求明确 #

需要从Table1返回满足以下规则的行：

• 当typenum ≠ 25时，该行的日期范围必须与同uid的Table2日期范围存在重叠；
• 当typenum = 25时，该行的日期范围必须与同uid的Table2日期范围无重叠；

对照示例数据：

• 符合条件的行：非25类型的行均与Table2（uid=123，09/1/22-09/3/22）重叠；25类型的行均与该范围无重叠；
• 不符合条件的行：25类型的行均与该范围重叠。

原查询的问题 #

原查询仅筛选了typenum=25且与Table2日期范围重叠的行，完全违背需求，同时未处理typenum≠25的场景，无法正确得到目标结果。

优化简化方案 #

核心：简化日期重叠判断逻辑 #

两个日期范围[A_start, A_end]和[B_start, B_end]存在重叠的通用判断逻辑可以简化为：

A_start <= B_end AND A_end >= B_start

这个表达式覆盖了所有重叠场景（完全包含、部分重叠、交叉覆盖等），无需拆分多种分支，更简洁且性能更优。

完整优化查询 #

SELECT T1.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.uid = T2.uid
WHERE
    -- 非25类型：需与Table2日期范围重叠
    (T1.typenum != 25 AND T1.startdate <= T2.enddate AND T1.enddate >= T2.startdate)
    OR
    -- 25类型：需与Table2日期范围无重叠
    (T1.typenum = 25 AND NOT (T1.startdate <= T2.enddate AND T1.enddate >= T2.startdate))

可选：用CASE表达式统一逻辑 #

如果偏好更紧凑的逻辑书写方式，可使用CASE表达式：

SELECT T1.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.uid = T2.uid
WHERE
    CASE
        WHEN T1.typenum = 25 THEN NOT (T1.startdate <= T2.enddate AND T1.enddate >= T2.startdate)
        ELSE T1.startdate <= T2.enddate AND T1.enddate >= T2.startdate
    END

性能优化提示 #

• 为Table1(uid, typenum)和Table2(uid)创建复合/单列索引，加速关联与筛选操作；
• 若日期字段存储为字符串类型，需先转换为DATE类型再比较（如TO_DATE(T1.startdate, 'MM/DD/YY')），避免隐式转换导致索引失效。

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