求正确SQL连接结构：实现Location关联Room与Floor的指定输出

你需要保留所有room和floor的记录，仅当room_key与floor_key、location_key同时匹配时关联显示，否则对应字段填充null。核心是用全外连接（FULL OUTER JOIN），或者在不支持该语法的数据库中用UNION ALL模拟。

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

SELECT
    l.location_key AS "Location",
    r.room_key AS "Room",
    f.floor_key AS "floor"
FROM
    Location l
LEFT JOIN room r ON l.location_key = r.location_key
FULL OUTER JOIN floor f ON 
    l.location_key = f.location_key 
    AND COALESCE(r.room_key, -1) = COALESCE(f.floor_key, -1)
WHERE
    l.location_key = 'ABC';

方案2：模拟全外连接（适用于MySQL等不支持FULL OUTER JOIN的数据库） #

-- 先取所有room的记录，关联对应floor
SELECT
    l.location_key AS "Location",
    r.room_key AS "Room",
    f.floor_key AS "floor"
FROM
    Location l
JOIN room r ON l.location_key = r.location_key
LEFT JOIN floor f ON 
    r.location_key = f.location_key 
    AND r.room_key = f.floor_key
WHERE
    l.location_key = 'ABC'

-- 再取没有对应room的floor记录
UNION ALL

SELECT
    l.location_key AS "Location",
    NULL AS "Room",
    f.floor_key AS "floor"
FROM
    Location l
JOIN floor f ON l.location_key = f.location_key
WHERE
    l.location_key = 'ABC'
    AND f.floor_key NOT IN (SELECT room_key FROM room WHERE location_key = 'ABC');

输出结果 #

执行后会得到你期望的格式：

Location:ABC Room:1    floor:null
Location:ABC Room:2    floor:2
Location:ABC Room:3    floor:null
Location:ABC Room:null floor:4
Location:ABC Room:null floor:5

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