您可以使用LEFT OUTER JOIN和FULL OUTER JOIN以及括号的组合来指定联接中的优先级：

SELECT l.location_key,
       f.floor_key,
       r.room_key
FROM   Location l
       LEFT OUTER JOIN (
         room r
         FULL OUTER JOIN floor f
         ON  r.location_key = f.location_key
         AND r.room_key = f.floor_key
       )
       ON (  l.location_key = r.location_key
          OR l.location_key = f.location_key)

其中，对于样本数据：

CREATE TABLE location(location_key) AS
SELECT 'abc' FROM DUAL;

CREATE TABLE floor (location_key, floor_key) AS
SELECT 'abc', 1 FROM DUAL UNION ALL
SELECT 'abc', 2 FROM DUAL UNION ALL
SELECT 'abc', 3 FROM DUAL;

CREATE TABLE room(location_key, room_key) AS
SELECT 'abc', 2 FROM DUAL UNION ALL
SELECT 'abc', 4 FROM DUAL UNION ALL
SELECT 'abc', 5 FROM DUAL;

输出：
| 位置_键|楼层键|房间钥匙|
| --|--|--|
| ABC| 1 |* 空 |
| ABC| 2 | 2 |
| ABC| 3 | 空 *|
| ABC| * 空 *| 4 |
| ABC| * 空 *| 5 |
fiddle

你似乎想要一个完整的外部连接的flloors和房间，以便不仅得到地板/房间匹配，但也有地板没有房间和房间没有地板。

select
  coalesce(f.location_key, r.location_key) as location,
  r.room_key,
  f.floor_key
from room r
full outer join floor f  on f.location_key = r.location_key
                        and f.floor_key    = r.room_key
order by location, r.room_key, f.floor_key;

（将楼层键与房间键进行比较看起来有点奇怪，但我猜这只是由于不幸的命名选择。