Oracle outer join by (+) to Postgres conversion I migrated from the Oracle database to Postgres, but some SQL scripts crashed. I investigated the errors and I found out that Postgres doesn't support outer joins using by (+) convention.
I tried to overwrite this Oracle statement:
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 &lt;&gt; f.sid
  AND r1.fibre = 123456;

to Postgres statement:
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 &lt;&gt; f.sid
  AND r1.fibre = 123456;

according to Postgres Wiki but in console I get this error: relation &quot;r2&quot; does not exist
Is possible to make joins between sub-select statements?
Or is in the script any error?
All those derived tables are useless (even in Oracle).
As far as I can tell, this should be equivalent:
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 &lt;&gt; f.sid;

I think the condition r1.fibre &lt;&gt; f.sid in the WHERE clause was wrong as it turned the outer join back into an inner join.