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.

You asked a very similar question here: stackoverflow.com/questions/2268732/… — Mark Byers
– Mark Byers, Commented Feb 15, 2010 at 21:49

Quassnoi · Accepted Answer · 2010-02-15 21:48:47Z

2

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.

answered Feb 15, 2010 at 21:48

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Walter Mitty · Accepted Answer · 2010-02-16 09:04:57Z

0

(+) 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.

answered Feb 16, 2010 at 9:04

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

2 Comments

Quassnoi Over a year ago

The original programmer uses predicates like MO.MO_MSN_CLASS_NM = 'TOP' which can be never satisfied for the fake records returned by the LEFT JOIN. He probably had some reasons indeed, but the query as he wrote it (not as he meant it) is identical to the INNER JOIN.

HLGEM Over a year ago

When you use a where clause (other than something like where b.my_id is null) that is from the table in the right side of a left join (the one you expect to get the null records from), you convert it to an inner join. This was probably not intentional, but it is what the query would return. These conditions should be in the join (for example:on a.myid = b.myid and b.myfield = 2) to keep the left join as a left join.

Collectives™ on Stack Overflow

Converting from Oracle Join to Postgres Join

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related