你可以这样做：

SELECT *
FROM Payrollrecords 
WHERE start_date > TO_DATE('2021-11-20','YYYY-MM-DD')
AND end_date <= TO_DATE('2022-11-30','YYYY-MM-DD');

RosterStartDate为08-OCT-21，RosterEndDate为NULL。即使RosterEndDate不为null，但假设为23-DEC-21，您也不会找到一行将两个日期都与

where START_DATE = (select START_DATE from RosterStartDate)
  and END_DATE = (select END_DATE from RosterEndDate)

你想要这个代替：

where START_DATE >= (select START_DATE from RosterStartDate)
  and END_DATE <= (select END_DATE from RosterEndDate)

但由于END_DATE可以为空，因此您最终需要：

where START_DATE >= (select START_DATE from RosterStartDate)
  and
  (
    END_DATE <= (select END_DATE from RosterEndDate)
    or
    (select END_DATE from RosterEndDate) is null
  )

更容易写成

select *
from Payrollrecords
where START_DATE >= (select START_DATE from RosterStartDate)
and END_DATE <= (select nvl(END_DATE, Payrollrecords.END_DATE) from RosterEndDate)

或者使用可能更快更清楚的不可达日期文字：

select *
from Payrollrecords
where START_DATE >= (select START_DATE from RosterStartDate)
and END_DATE <= (select nvl(END_DATE, date '9999-12-31') from RosterEndDate)

演示：https://dbfiddle.uk/taPsiGJw

我不确定是否正确理解了你问题的全部内容。
工资单记录表示日期间隔。您希望查找与间隔（RosterStartDate - RosterEndDate）相交的所有工资单记录，其中RosterEndDate可能为NULL。如果为NULL，我们可以使用NVL()函数将其替换为今天的值。因此，类似于以下内容的代码应该可以完成这项工作

SELECT * FROM Payrollrecords
WHERE START_DATE <= NVL((select END_DATE from RosterEndDate), CURRENT_DATE)
  AND END_DATE >= (select START_DATE from RosterStartDate)

你的问题写得很混乱然而，从你评论来看，你似乎只是想：

SELECT *
FROM   Payrollrecords
WHERE  start_date <= DATE '2022-11-30'
AND    end_date   >= DATE '2021-11-20'

如果日期包含时间部分，并且您希望2022 - 11 - 30为全天，则使用：

SELECT *
FROM   Payrollrecords
WHERE  start_date <  DATE '2022-11-31'
AND    end_date   >= DATE '2021-11-20'

原始答案

如果使用分析函数，则可以消除所有子查询。
如果要使所有范围完全包含在范围内，则：

SELECT *
FROM   (
  SELECT p.*,
         MAX(CASE WHEN start_date <  DATE '2021-11-20' THEN start_date END) OVER ()
           AS max_start_date,
         COALESCE(
           MIN(CASE WHEN end_date   >= DATE '2022-11-30' THEN end_date   END) OVER (),
           DATE '9999-12-31' -- Or SYSDATE
         ) AS min_end_date
  FROM   Payrollrecords p
)
WHERE  start_date >= max_start_date
AND    end_date   <= min_end_date;

如果您希望范围与该范围重叠，则：

SELECT *
FROM   (
  SELECT p.*,
         MAX(CASE WHEN start_date <  DATE '2021-11-20' THEN start_date END) OVER ()
           AS max_start_date,
         COALESCE(
           MIN(CASE WHEN end_date   >= DATE '2022-11-30' THEN end_date   END) OVER (),
           DATE '9999-12-31' -- Or SYSDATE
         ) AS min_end_date
  FROM   Payrollrecords p
)
WHERE  start_date <= min_end_date
AND    end_date   >= max_start_date;

fiddle

我建议您在数据中包含一个end_date为NULL的行，如下所示：

CREATE TABLE Payrollrecords  (
  leave_type VARCHAR2(50),
  start_date DATE,
  end_date DATE,
  leave_number NUMBER
);

BEGIN
  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-10-08', DATE '2021-10-08', 24042);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-11-21', DATE '2021-11-29', 24043);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-23', DATE '2021-12-23', 30069);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-23', NULL, 99999);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-29', DATE '2021-12-31', 30112);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2022-01-24', DATE '2022-01-24', 30189);
END;
/

select * 
from Payrollrecords

| 休假类型|开始日期|结束日期|休假编号|
| - ------|- ------|- ------|- ------|
| 年假|2021年10月8日|2021年10月8日|小行星24|
| 年假|2021年11月21日|二十一年十一月二十九日|小行星24043|
| 年假|二十一年十二月二十三日|二十一年十二月二十三日|小行星30069|
| 年假|二十一年十二月二十三日|* 无效 *|小九九九九九|
| 年假|二十一年十二月二十九日|二十一年十二月三十一日|小行星30112|
| 年假|2022年1月24日|2022年1月24日|小行星30189|

select * 
from Payrollrecords
where start_date <= TO_DATE('2022-11-30','YYYY-MM-DD') --range_end
and (end_date >= TO_DATE('2021-11-20','YYYY-MM-DD') --range_start
    or end_date IS NULL
    )

| 休假类型|开始日期|结束日期|休假编号|
| - ------|- ------|- ------|- ------|
| 年假|2021年11月21日|二十一年十一月二十九日|小行星24043|
| 年假|二十一年十二月二十三日|二十一年十二月二十三日|小行星30069|
| 年假|二十一年十二月二十三日|* 无效 *|小九九九九九|
| 年假|二十一年十二月二十九日|二十一年十二月三十一日|小行星30112|
| 年假|2022年1月24日|2022年1月24日|小行星30189|
请参见此fiddle
注：
1.比较start_date和range_end，以及end_date和range_start看起来似乎违反直觉，但是这种方法有效并且简化了所需的 predicate
1.当您在CTE中包含日期文字时，只需在where子句中使用相同的文字并避免CTE-绑定变量在这里也可以工作
1.要考虑NULL，请添加"或end_date ISNULL"，并使用适当的括号，不要在 predicate 中使用NVL或COALESCE，因为这些函数可能对查询性能有害。