Oracle 外連接 (+) 到 Postgres 的轉換

我從 Oracle 數據庫遷移到 Postgres，但是一些 SQL 腳本崩潰了。我調查了這些錯誤，發現 Postgres 不支持使用 by (+) 約定的外連接。

我試圖覆蓋這個 Oracle 語句：

SELECT
      r1.fibre AS root_edge_sid,
      sp.sid AS node_sid,
      sp.fid AS node_fid,
      sp.ftid AS node_ftid,
      f.sid AS edge_sid,
      f.fid AS edge_fid,
      f.ftid AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
    (SELECT sf.SID AS sid, sf.FID AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
    (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
    (SELECT f.SID AS sid, f.FID AS fid, f.FTID AS ftid FROM TABLE_C f) f
WHERE r1.splice_point = sp.sid
 AND sp.sid = r2.splice_point(+)
 AND r2.fibre = f.sid(+)
 AND r1.fibre <> f.sid
 AND r1.fibre = 123456;

到 Postgres 聲明：

SELECT r1.fibre AS root_edge_sid,
      sp.sid   AS node_sid,
      sp.fid   AS node_fid,
      sp.ftid  AS node_ftid,
      f.sid    AS edge_sid,
      f.fid    AS edge_fid,
      f.ftid   AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
    (SELECT sf.SID AS sid, sf.FID  AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
    (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
    (SELECT f.SID  AS sid, f.FID  AS fid, f.FTID AS ftid FROM TABLE_C f) f
left outer join r2
on sp.sid = r2.splice_point
left outer join f
on r2.fibre = f.sid
WHERE r1.splice_point = sp.sid
 AND r1.fibre <> f.sid
 AND r1.fibre = 123456;

根據Postgres Wiki但在控制台中我收到此錯誤：關係“r2”不存在

是否可以在子選擇語句之間進行連接？還是腳本中有任何錯誤？

所有這些派生表都是無用的（即使在 Oracle 中）。

據我所知，這應該是等價的：

SELECT r1.fibre AS root_edge_sid,
      sp.sid   AS node_sid,
      sp.fid   AS node_fid,
      sp.ftid  AS node_ftid,
      f.sid    AS edge_sid,
      f.fid    AS edge_fid,
      f.ftid   AS edge_ftid
FROM table_a r1
 JOIN table_b sp ON r1.splice_point = sp.sid
 LEFT JOIN table_a r2 ON sp.sid = r2.splice_point
 LEFT JOIN table_c f ON r2.fibre = f.sid
WHERE r1.fibre = 123456
AND r1.fibre <> f.sid;

我認為r1.fibre <> f.sidWHERE 子句中的條件是錯誤的，因為它將外連接重新變為內連接。

引用自：https://dba.stackexchange.com/questions/279408