Using self join in Postgres

Question

How can I create the below JOIN in Postgres , This code is from Oracle and it has one join condition which I am not familiar with.

T3.u_ffm_sub_id(+) = T3.u_ffm_id(+)

It is like self join but (+) is on both the sides of condition, So I am confused how to create same in postgres.

Below is the complete code:

SELECT DISTINCT
   CASE
       WHEN MD.u_ffm_id = MD.u_ffm_sub_id THEN MD.patient
       ELSE T3.patient
   END
INTO lRet
FROM UPI.memdate MD,
   UPI.memdate T3
WHERE T3.hmo           = pHmo
AND T3.u_ffm_sub_id(+) = T3.u_ffm_id(+)
AND MD.hmo             = T3.HMO(+)
AND MD.u_ffm_sub_id    = T3.u_ffm_sub_id(+)
AND md.patient         = pPatient;

That's Oracle's proprietary (deprecated) way of writing an outer join — user330315
– user330315, Commented Dec 2, 2021 at 12:22

Laurenz Albe · Accepted Answer · 2021-12-02 12:36:42Z

2

These are outer joins, and they are easy to translate to standard conforming SQL.

If the (+) is on the right side:

FROM a, b
WHERE a.x = b.y(+)

gets translated to

FROM a LEFT JOIN b
   ON a.x = b.y

If the (+) is on the other side, it would be a RIGHT JOIN.

If the (+) is on both sides:

FROM a, b
WHERE a.x(+) = b.y(+)

gets translated to

FROM a FULL JOIN b
   OM a.x = b.y

answered Dec 2, 2021 at 12:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Using self join in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related