Converting from Oracle Join to Postgres Join

Question

I have two select statements that I am trying to port from Oracle to Postgres:

1) (Note: this is a subselect part of a bigger select)

SELECT 'Y'
FROM CRAFT MA, CONFIG MAC, ARMS SM
WHERE MCI.MS_INVENTORY_NUMBER = SM.MS_INVENTORY_NUMBER (+)
AND MCI.AB_BASE_ID = MA.AB_BASE_ID_LAUNCH AND SM.ACT_AC_TYPE = MAC.ACT_AC_TYPE
AND SM.MAC_ID = MAC.MAC_ID AND MAC.ACT_AC_TYPE = MA.ACT_AC_TYPE
AND MAC.MAC_ID = MA.MAC_ID_PRI

2)

SELECT ASP.ASP_SPACE_NM,
       SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) AS "TOTAL_PLANNED"
  FROM MISSION_OBJECTIVE MO, SPACE ASP
 WHERE ASP.ASP_SPACE_NM = MO.ASP_SPACE_NM (+)
   AND MO.MO_MSN_CLASS_NM = 'TOP'
 GROUP BY ASP.ASP_SPACE_NM

The (+) syntax is confusing for me... I know it signifies a "join", but I am not familiar enough with SQL to understand what is equivalent to what.

Answer 1

SELECT  'Y'
FROM    CRAFT MA
JOIN    CONFIG MAC
ON      MAC.ACT_AC_TYPE = MA.ACT_AC_TYPE
        AND MAC.MAC_ID = MA.MAC_ID_PRI
        AND MA.AB_BASE_ID_LAUNCH = MCI.AB_BASE_ID
LEFT JOIN
        ARMS SM
ON      SM.MS_INVENTORY_NUMBER = MCI.MS_INVENTORY_NUMBER
WHERE   SM.ACT_AC_TYPE = MAC.ACT_AC_TYPE
        AND SM.MAC_ID = MAC.MAC_ID AND 

and

SELECT ASP.ASP_SPACE_NM,
       SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) AS "TOTAL_PLANNED"
FROM   SPACE ASP
LEFT JOIN
       MISSION_OBJECTIVE MO
ON     MO.ASP_SPACE_NM = ASP.ASP_SPACE_NM
WHERE  MO.MO_MSN_CLASS_NM = 'TOP'
GROUP BY
       ASP.ASP_SPACE_NM

I left the LEFT JOIN as it was in the original query, but it's redundant here due to the WHERE condition.

You may replace it with an INNER JOIN or just drop the (+) part.

Answer 2

(+) was Oracle's way of expressing an outer join before "LEFT JOIN" (etc.) was added to standard SQL. Some Oracle practitioners did not immediately switch over to the standard syntax, even after it was added to the Oracle dialect of SQL.

The answer you have accepted says that you can replace "LEFT JOIN" with "INNER JOIN". I'm not convinced. But I'm confused by the reference to "MCI" in the first query.

The original programmer probably had a reason for using outer join instead of inner join. You might want to check and see whether that original reason was valid.