Replacing oracle specific notation (+) with LEFT JOIN and where clause

Question

Lately the (+) has been causing some issues for me, maybe it's depreciated by now. Now I want to replace the (+) notation with LEFT JOIN, but I can't seem to get the syntax in order.

This is what I have:

SELECT x.a, y.a, z.a
FROM x, y, z
WHERE x.k = y.k(+)
AND x.p = z.p(+);

How can I replace the (+) with a LEFT JOIN?

-EDIT- I've read the "duplicated" answers, and few of them have accepted answers (no wonder why)

I could not solve my problem according to those. The answer provided here was spot on and solved it - marked it as accepted.

I got another one that is a little bit more confusing (to me)

FROM a,b,c
WHERE b.a  = '101'
AND a.a    = '202'
AND b.c    = a.c
AND a.d    = c.d(+)
AND ROWNUM = 1;

Again, I want to replace the (+) with LEFT JOIN

Start with 2 tables. Do `x left join y on x.k = y.k` and `y left join x on x.k = y.k` - which one returns the wanted result? — jarlh, Aug 18 '21 at 12:49
SELECT x.a, y.a, z.a FROM x left join y on x.k = y.k left join z on x.p = z.p; — Kazi Mohammad Ali Nur Romel, Aug 18 '21 at 12:56
After your edit, you seem to be asking a new question. Best to do this as a brand new question and link back to this one if relevant. — Simon.S.A., Aug 20 '21 at 05:15

score 1 · Accepted Answer · answered Aug 18 '21 at 12:56

1

The equivalent syntax would be:

SELECT x.a, y.a, z.a
FROM x LEFT JOIN
     y
     ON x.k = y.k LEFT JOIN
     z
     ON x.p = z.p;

answered Aug 18 '21 at 12:56

Gordon Linoff

1,242,037
58
646
786

Replacing oracle specific notation (+) with LEFT JOIN and where clause

1 Answers1