如何通过同表多连接获取符合出发日期的预订数据（含返程ID为空）

数据表结构与测试数据 #

现有RESERVATION（预订）和TRAVEL（行程）两张表，其中RESERVATION的trav_go_id（去程ID）、trav_ret_id（返程ID，可空）关联TRAVEL的trav_id字段。建表及测试数据插入语句如下：

CREATE TABLE RESERVATION (
  RESE_ID NUMBER(10, 0) NOT NULL 
, TRAV_GO_ID NUMBER(10, 0) NOT NULL 
, TRAV_RET_ID NUMBER(10, 0) -- 该字段允许为空
);
    
CREATE TABLE TRAVEL (
  TRAV_ID NUMBER(10, 0) NOT NULL   -- 对应RESERVATION的去程或返程ID
, DEPARTURE_DATE_TIME DATE 
, ARRIVAL_DATE_TIME DATE 
);

-- 插入预订数据
insert into RESERVATION(RESE_ID,TRAV_GO_ID,TRAV_RET_ID) values ( 1, 222, 555);
insert into RESERVATION(RESE_ID,TRAV_GO_ID,TRAV_RET_ID) values ( 2, 333, null);
insert into RESERVATION(RESE_ID,TRAV_GO_ID,TRAV_RET_ID) values ( 3, 444, null);

-- 插入行程数据
insert into TRAVEL(TRAV_ID,DEPARTURE_DATE_TIME,ARRIVAL_DATE_TIME) values 
( 222, (TO_DATE('2024/08/29 01:02:44', 'yyyy/mm/dd hh24:mi:ss')), (TO_DATE('2024/08/29 21:02:44', 'yyyy/mm/dd hh24:mi:ss')));
    
insert into TRAVEL(TRAV_ID,DEPARTURE_DATE_TIME,ARRIVAL_DATE_TIME) values 
( 444, (TO_DATE('2024/08/27 01:02:44', 'yyyy/mm/dd hh24:mi:ss')), (TO_DATE('2024/08/27 21:02:44', 'yyyy/mm/dd hh24:mi:ss')));

insert into TRAVEL(TRAV_ID,DEPARTURE_DATE_TIME,ARRIVAL_DATE_TIME) values 
( 333, (TO_DATE('2024/08/29 01:02:44', 'yyyy/mm/dd hh24:mi:ss')),null);

需求说明 #

查询两张表的全部详情，筛选条件为TRAVEL表的departure_date_time大于当前日期。例如当前日期为2024/08/28时，需获取预订ID为1、2的记录。

原SQL问题分析 #

原SQL使用两次INNER JOIN关联行程表：

select count(*) from RESERVATION r inner  join travel t on r.trav_go_id = t.trav_id
inner  join travel tt on r.trav_ret_id = tt.trav_id

由于TRAV_RET_ID允许为空，INNER JOIN会过滤掉所有trav_ret_id为null或对应行程不存在的预订记录，导致结果不符合需求。

正确SQL写法 #

将返程行程的关联改为LEFT JOIN，保留所有符合去程条件的预订记录，即使返程ID为空或无对应行程：

SELECT 
    r.*,
    t.trav_id AS go_trav_id,
    t.departure_date_time AS go_departure,
    t.arrival_date_time AS go_arrival,
    tt.trav_id AS ret_trav_id,
    tt.departure_date_time AS ret_departure,
    tt.arrival_date_time AS ret_arrival
FROM RESERVATION r
LEFT JOIN TRAVEL t ON r.trav_go_id = t.trav_id
LEFT JOIN TRAVEL tt ON r.trav_ret_id = tt.trav_id
WHERE t.departure_date_time > TRUNC(SYSDATE); -- 筛选去程出发时间大于当前日期（TRUNC(SYSDATE)取当前日期的零点）

说明 #

1. LEFT JOIN关联返程行程：确保trav_ret_id为空或无对应行程的预订记录不会被过滤。
2. 过滤条件WHERE t.departure_date_time > TRUNC(SYSDATE)：保证只保留去程出发时间晚于当前日期的预订，符合需求示例中的结果（预订ID1、2的去程均在2024/08/29，晚于2024/08/28）。
3. 若需要统计符合条件的记录数量，可将SELECT部分改为COUNT(r.rese_id)。

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