SQL查询未返回预期结果，求修改语句以获取目标输出

原始查询语句 #

select
    bookings.id as departure,
    return_detail .id as return_id
from
    bookings
left join bookings return_detail on
    bookings.trip_id = return_detail .trip_id
left join booking_passengers bp on
    bookings.id = bp.bp_booking_id
left join booking_passengers return_bp on
    return_detail .id = return_bp .bp_booking_id
where
    bp.bp_passenger_id = 31
    and return_bp .bp_passenger_id = 31
    --and bp.id != return_bp .id
order by
    bookings.id asc

预期输出 #

41-42
43-44
null-58
83-84
91-null

问题分析与修改方案 #

原始查询的核心问题是WHERE子句同时过滤了bp和return_bp的乘客ID，这会把左连接强制转为内连接，导致只有同时存在去程、返程乘客记录的订单才会被保留，无法得到null-58或91-null这类单边存在的结果。

修改后的查询语句：

select
    b.id as departure,
    rd.id as return_id
from
    bookings b
left join bookings rd 
    on b.trip_id = rd.trip_id 
    and b.id != rd.id -- 排除同一订单自关联的情况
left join booking_passengers bp 
    on b.id = bp.bp_booking_id 
    and bp.bp_passenger_id = 31 -- 在关联时就过滤去程的目标乘客
left join booking_passengers return_bp 
    on rd.id = return_bp.bp_booking_id 
    and return_bp.bp_passenger_id = 31 -- 在关联时就过滤返程的目标乘客
where
    -- 确保结果仅包含目标乘客的订单（至少一个方向存在该乘客）
    bp.bp_passenger_id is not null 
    or return_bp.bp_passenger_id is not null
order by
    coalesce(b.id, rd.id) asc -- 按存在的ID排序，保证结果顺序符合预期

修改说明 #

1. 乘客过滤移至JOIN ON子句：保留左连接的特性，不会过滤掉只有去程或只有返程的订单。
2. 添加自关联排除条件：避免同一订单被关联到自身，防止错误匹配。
3. WHERE子句过滤无效记录：排除无关订单，确保结果仅包含目标乘客的订单。
4. 优化排序逻辑：用coalesce函数让单边存在的订单也能按合理顺序排列。

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