SQLite三张Mission表关联查询结果不符合预期如何解决

问题分析 #

• 现有查询仅关联了mission1_1、mission2_2两张表，未加入Mission3对应的数据，无法输出M3字段内容
• 使用INNER JOIN关联仅会保留三张表中时间戳完全匹配的记录，若某一时间戳仅在单张/两张表中存在，会被直接过滤，不符合全时间序列展示的预期

调整方案 #

方案1：支持FULL OUTER JOIN的数据库（PostgreSQL、SQL Server等适用） #

使用全外连接保留所有表的时间戳记录，通过COALESCE函数补全非空的任务标识和时间戳：

SELECT 
  COALESCE(m1.missionIdentifier, m2.missionIdentifier, m3.missionIdentifier) AS missionIdentifier,
  m1.M1,
  m2.M2,
  m3.M3,
  COALESCE(m1.Timestamp, m2.Timestamp, m3.Timestamp) AS Timestamp
FROM mission1_1 m1
FULL OUTER JOIN mission2_2 m2 
  ON m1.Timestamp = m2.Timestamp
FULL OUTER JOIN mission3_3 m3 
  ON COALESCE(m1.Timestamp, m2.Timestamp) = m3.Timestamp
ORDER BY Timestamp ASC;

方案2：MySQL等不支持FULL OUTER JOIN的数据库适用 #

通过UNION ALL拼接三张表的记录，缺失的字段赋值为NULL：

SELECT missionIdentifier, M1, NULL AS M2, NULL AS M3, Timestamp FROM mission1_1
UNION ALL
SELECT missionIdentifier, NULL AS M1, M2, NULL AS M3, Timestamp FROM mission2_2
UNION ALL
SELECT missionIdentifier, NULL AS M1, NULL AS M2, M3, Timestamp FROM mission3_3
ORDER BY Timestamp ASC;

两种方案最终输出都会保留所有时间戳记录，对应表没有的M字段值自动显示为NULL，符合预期输出要求。

内容的提问来源于stack exchange，提问作者s.k Syed