如何在SQLite3中不使用RIGHT JOIN或FULL OUTER JOIN合并查询获取起止站点列

你不需要拆分两个查询再合并，直接通过关联两次stations表就能得到想要的结果，全程不用RIGHT JOIN或FULL OUTER JOIN，SQLite完全支持这种写法：

SELECT 
    start_station.name AS 起始站名称,
    end_station.name AS 终点站名称
FROM journey
INNER JOIN stations AS start_station ON journey.start_station_id = start_station.id
INNER JOIN stations AS end_station ON journey.end_station_id = end_station.id;

代码说明 #

• 给stations表起两个别名：start_station（对应行程的起始站）和end_station（对应行程的终点站），用来区分同一个表的两次关联
• 用INNER JOIN关联journey和两个stations实例，分别匹配起始站ID和终点站ID
• 通过别名分别取出两个站点的名称，可按需设置列名（比如示例里的起始站名称、终点站名称）

如果你的journey表中存在start_station_id或end_station_id为NULL的情况（即某个行程没有起始/终点站），而你想保留这些记录，可以把INNER JOIN换成LEFT JOIN，同样不需要用到禁止的连接类型：

SELECT 
    start_station.name AS 起始站名称,
    end_station.name AS 终点站名称
FROM journey
LEFT JOIN stations AS start_station ON journey.start_station_id = start_station.id
LEFT JOIN stations AS end_station ON journey.end_station_id = end_station.id;

内容的提问来源于stack exchange，提问作者Mohammad muazzam ali