Oracle SQL优化查询：日期范围记录获取及空值处理

问题背景 #

需要在Oracle SQL中使用变量varStartDate和varEndDate（均为DATE类型），获取处于该日期范围内的记录。现有查询逻辑为先获取早于指定日期的最近start_date、晚于等于指定日期的最早end_date，再筛选对应记录，但当没有符合条件的end_date时（即RosterEndDate.END_DATE为空），查询无结果，需要优化该查询。

示例数据 #

原查询代码 #

with RosterStartDate as (
  select max(start_date) as START_DATE
  from Payrollrecords
  where START_DATE < TO_DATE('2021-11-20','YYYY-MM-DD')
),
RosterEndDate as (
  select min(end_date) as END_DATE
  from Payrollrecords where END_DATE >= TO_DATE('2022-11-30','YYYY-MM-DD')
)
                
select *
from Payrollrecords
where START_DATE = (select START_DATE from RosterStartDate)
  and END_DATE = (select END_DATE from RosterEndDate)

问题分析 #

原查询的核心问题在于：当RosterEndDate中没有找到符合END_DATE >= varEndDate的记录时，END_DATE会返回NULL。而SQL中NULL与任何值的相等比较（=）结果都是UNKNOWN，导致WHERE条件不成立，最终无结果返回。同理，如果RosterStartDate中没有找到符合条件的记录，也会出现相同问题。

优化方案 #

以下两种方案可根据业务需求选择：

方案1：为无匹配的日期设置默认边界 #

当没有找到符合条件的开始/结束日期时，取表中对应的最小/最大日期作为默认边界，保证CTE始终返回有效日期，避免NULL导致的查询失效。

with RosterStartDate as (
  select coalesce(
    max(start_date),
    (select min(start_date) from Payrollrecords)
  ) as START_DATE
  from Payrollrecords
  where START_DATE < :varStartDate -- Oracle绑定变量写法
),
RosterEndDate as (
  select coalesce(
    min(end_date),
    (select max(end_date) from Payrollrecords)
  ) as END_DATE
  from Payrollrecords 
  where END_DATE >= :varEndDate -- Oracle绑定变量写法
)
                
select *
from Payrollrecords
where START_DATE = (select START_DATE from RosterStartDate)
  and END_DATE = (select END_DATE from RosterEndDate)

方案2：无匹配时跳过对应筛选条件 #

如果业务允许某一边界无匹配时放宽筛选（比如没有符合的结束日期时，只筛选匹配开始日期的记录），可以修改WHERE子句，判断子查询结果是否为NULL，为NULL时跳过该条件。

with RosterStartDate as (
  select max(start_date) as START_DATE
  from Payrollrecords
  where START_DATE < :varStartDate
),
RosterEndDate as (
  select min(end_date) as END_DATE
  from Payrollrecords 
  where END_DATE >= :varEndDate
)
                
select *
from Payrollrecords
where (START_DATE = (select START_DATE from RosterStartDate) 
       OR (select START_DATE from RosterStartDate) IS NULL)
  and (END_DATE = (select END_DATE from RosterEndDate) 
       OR (select END_DATE from RosterEndDate) IS NULL)

补充说明 #

• :varStartDate和:varEndDate是Oracle标准绑定变量写法，可根据实际场景替换为PL/SQL变量或应用程序参数。
• 方案1适用于需要始终获取有效日期边界的场景，方案2适用于允许放宽筛选条件的场景。

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