查询同一航班全部乘客的SQL优化及WHERE子句使用方法咨询

• 表结构说明存在书写笔误：原文档中Airplane(airplane_id),airplane_name,modelno)的括号配对错误，实际建表时所有字段需统一放在表名后的同一对括号内，属于文档层面的低级错误。
• 关联类型选型错误：原语句全部使用LEFT JOIN关联，会引入大量无业务价值的无效数据——没有任何预订记录的乘客，在关联表字段上全为NULL，这类记录不属于任何航班，留在结果里没有意义，这类强匹配的关联场景更适合用INNER JOIN。
• 缺失核心业务过滤：原语句只做了四张表的全量关联，没有任何过滤条件，实际会返回系统内所有乘客、所有航班的全部预订关联结果，根本没法定位到目标航班的乘客集合。
• 没有对齐“同一航班”的判定规则：结合给出的表结构，没有独立的航班ID字段，能唯一标识一班固定执飞航班的核心维度是「执飞飞机ID + 航班日期 + 执行航线」，原查询完全没有围绕这个维度组合设计匹配逻辑。

同一航班的判定必须同时满足三个核心条件：执飞飞机一致、航班出发日期一致、执行航线一致，三者缺一不可，否则会把不同飞机飞同一条航线、同一飞机不同日期飞同一条航线的记录错判为同一航班。

WHERE子句的编写可以根据实际检索场景调整：

• 场景1：已知某条具体预订记录，要检索和该订票人同航班的所有乘客
  可以通过子查询提取该预订对应的三个核心航班标识，再做匹配，参考SQL如下：

SELECT P.*, B.flight_date, A.airplane_name, D.route
FROM Passenger AS P
INNER JOIN Booking AS B 
  ON P.id = B.passenger_id
INNER JOIN Destinations AS D 
  ON D.id = B.destination_id
INNER JOIN Airplane AS A 
  ON A.airplane_id = D.airplane_id
WHERE
  A.airplane_id = (
    SELECT D1.airplane_id 
    FROM Booking B1
    INNER JOIN Destinations D1 ON B1.destination_id = D1.id
    WHERE B1.booking_id = 123 -- 替换为实际已知的目标预订ID
  )
  AND B.flight_date = (
    SELECT B1.flight_date
    FROM Booking B1
    INNER JOIN Destinations D1 ON B1.destination_id = D1.id
    WHERE B1.booking_id = 123
  )
  AND D.route = (
    SELECT D1.route
    FROM Booking B1
    INNER JOIN Destinations D1 ON B1.destination_id = D1.id
    WHERE B1.booking_id = 123
  )

• 场景2：已知具体的航班参数（指定飞机、出发日期、航线）
  可以直接把参数值写入WHERE子句，不需要嵌套子查询，降低查询开销：

WHERE
  A.airplane_id = 5 -- 替换为实际执飞飞机ID
  AND B.flight_date = '2024-06-15' -- 替换为实际航班出发日期
  AND D.route = '北京-上海' -- 替换为实际执行航线

长期设计优化建议：当前表结构缺少独立的航班维度，后续可新增独立的Flight表存储单班航班信息（flight_id、airplane_id、flight_date、route），在Booking表中关联flight_id字段，能大幅简化同航班检索逻辑，降低多字段匹配带来的错判风险。

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