Oracle SQL: How to convert multiple (+) WHERE clauses to LEFT JOINS?

Question

I'm trying to convert some Oracle SQL code to regular SQL and am having trouble with the numerous variables in the SELECT statement, tables in the FROM statement, and (+) WHERE statements.

I have looked through similar posts but all provide simple examples with just 2 variables, 2 tables, and 1 (+) WHERE statement. I'm not sure how to apply what I'm seeing from the other forum posts to multiple variables, tables, and (+) WHERE statements.

SELECT 
    a.ID,
    c.var1,
    d.var2
FROM a, b, c, d, x, y
WHERE a.ID(+) = b.ID
    AND c.var1(+) = b.var1
    AND x.id(+) = y.id;

I tried to convert all the (+) WHERE statements to LEFT JOINs as shown below. I did 3 LEFT JOINS, one for each (+) WHERE statement:

SELECT 
    a.ID,
    c.var1,
    d.var2
FROM a, b, c, d, x, y
WHERE b.ID in 
    (SELECT b.ID
    FROM b
    LEFT JOIN a
    ON a.ID = b.ID)

FROM a, b, c, d, x, y. I guess, you should have 5 JOINS, but you provide much less. — Sergey, Dec 23 '22 at 19:26
https://stackoverflow.com/questions/43256158/to-join-5-tables-how-many-minimum-no-of-joins-are-required — Sergey, Dec 23 '22 at 19:38
One query. In your case I guess it could look like FROM a LEFT JOIN b ON a.ID=b.ID LEFT JOIN c ON b.var1=c.var1 ... But x and y are not related to a,b,c and it looks suspicious — Sergey, Dec 23 '22 at 19:53
@Sergey is correct, there are 5 joins, three `LEFT OUTER JOIN`s and two `CROSS JOIN`s — MT0, Dec 23 '22 at 21:19
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Dec 25 '22 at 06:34

MT0 · Accepted Answer · 2022-12-23T21:58:42.070

The query:

SELECT a.ID,
       c.var1,
       d.var2
FROM   a, b, c, d, x, y

can be rewritten using ANSI-join syntax as:

SELECT a.ID,
       c.var1,
       d.var2
FROM   a
       CROSS JOIN b
       CROSS JOIN c
       CROSS JOIN d
       CROSS JOIN x
       CROSS JOIN y

If you add in the WHERE clause then WHERE c.var1(+) = b.var1 converts the CROSS JOIN to a LEFT OUTER JOIN from b to c. Similarly for the other join conditions.

Which gives you:

SELECT a.ID,
       c.var1,
       d.var2
FROM   b
       LEFT OUTER JOIN a ON a.ID = b.ID
       LEFT OUTER JOIN c ON c.var1 = b.var1
       CROSS JOIN d
       CROSS JOIN y
       LEFT OUTER JOIN x ON x.id = y.id;

fiddle

Oracle SQL: How to convert multiple (+) WHERE clauses to LEFT JOINS?

1 Answers1