Converting multiple SQL implicit joins into explicit joins

oraclesql-standard

  • I am trying to convert several queries that use implicit joins into explicit joins, as per the standard ANSI/ISO Entry SQL-92 [1].
  • But after a lot of googling I could not find any examples or guides on how to proceed with the same. Could someone point me in the right direction..? Thanks!

Sample code:

    SELECT *
    FROM a,b,c,d,e,f,g
    WHERE
01:     e.p_id(+)   = a.p_id
02: AND e.o_id(+)   = a.o_id
03: AND c.c_id      = h.c_id(+)
04: AND c.b_id      = h.d_id(+)
05: AND g.d_id (+)  = h.d_id
06: AND g.o_id (+)  = h.o_id
07: AND a.p_id      = b.p_id
08: AND c.p_id      = a.p_id
09: AND d.c_id      = c.c_id
10: AND a.o_id      = f.e_id
11: AND g.o_id      = a.o_id

UPDATE

Based on my understanding of Ted Elliot's answer, I have tried to reproduce the algorithm:

But not clear on the steps 9, 10.

1.List all tables alphabetically: a,b,c,d,e,f,g

2.Start with first table 'a'. Identify all join conditions: 'a' joins with b/c/e/f/g. Pick any one, say 'e'. Keep 'a' to the right.

e
RJ a ON 
e.p_id = a.p_id AND 
e.o_id = a.o_id

3.Next is 'b'. Is 'b' joined with a/e above? Yes -> Continue.
Identify all join conditions: 'b' joins with a. Keep 'b' to the right.

...
INNER JOIN b ON
a.p_id = b._pid

4.Next is 'c'. Is c joined with a/b/e from above? Yes -> Continue.
Identify all join conditions: 'c' joins with h/a/d. Selected 'a'. Keep 'c' to the right.

...
INNER JOIN c on
c.p_id = a.p_id

5.Next is 'd'. Is d joined with a/b/c/e from above? Yes -> Continue.
Identify all join conditions: 'd' joins with c. Keep 'd' to the right.

...
INNER JOIN d on
d.c_id = c.c_id

6.Next is 'e'. Is e joined with a/b/c/d/e from above? e already exist in list -> Skip.

7.Next is 'f'. Is f joined with a/b/c/d/e from above? Yes -> Continue.
Identify all join conditions: 'f' joins with a. Keep 'f' to the right.

...
INNER JOIN f on
a.o_id = f.e_id

8.Next is 'g'. Is g joined with a/b/c/d/e/f from above? Yes -> Continue.
Identify all join conditions: 'g' joins with a/h. Selected 'a'. Keep 'g' to the right.

...
INNER JOIN g on
g.o_id = a.o_id

9.Next is 'h'. Is h joined with a/b/c/d/e/f/g from above? Yes -> Continue.
Identify all join conditions: 'h' joins with c/g. Selected 'c'. Keep 'h' to the right.
Note that the conditions require all records of 'c' to be joined with 'h': "c.c_id = h.c_id(+) AND c.b_id = h.d_id(+)". How can we ensure this as 'c' has been joined with a/d above so not all records of 'c' are present in the temp. table of above joins?

...
INNER JOIN h on
c.c_id      = h.c_id AND
c.b_id      = h.d_id

10.Table list completed, But there are two conditions remaining: "g.d_id (+) = h.d_id AND g.o_id (+) = h.o_id". Identify all join conditions: 'g' joins with h. Keep 'g' to the right. Note that the conditions require all records of 'h' to be joined with 'g'. But again h is joined above so not all records are available. 

...
INNER JOIN g on
g.d_id = h.d_id AND
g.o_id = h.o_id

Best Answer

This is how I would rewrite it:

SELECT *
FROM e
RIGHT OUTER JOIN a ON
  e.p_id = a.p_id AND 
  e.o_id = a.o_id
INNER JOIN b ON
a.p_id = b._pid
INNER JOIN c ON
a.p_id = c.p_id
INNER JOIN d ON
c.c_id = d.c_id
INNER JOIN f ON
a.o_id = f.e_id
INNER JOIN g ON
a.o_id = g.o_id
LEFT OUTER JOIN h ON
c.c_id = h.c_id
AND c.b_id = h.d_id
LEFT OUTER JOIN g ON
h.d_id = g.d_id
AND h.o_id = g.o_id

The (+) denote optional tables and end up being converted to either LEFT or RIGHT OUTER joins. In "c.c_id = h.c_id(+)" the "h" side is the optional one so this becomes "c LEFT OUTER JOIN h". If the "c" side had the plus instead it would be a "c RIGHT OUTER JOIN h", although I typically just swap the order and make it a LEFT OUTER JOIN since I think it is easier to read. I did that in a few cases (LEFT OUTER JOIN g).

If you have multiple conditions for the same set of tables those get combined in the ON clause: 

e.p_id(+) = a.p_id 
AND e.o_id(+) = a.o_id

becomes 

e RIGHT OUTER JOIN a ON 
e.p_id = a.p_id 
AND e.o_id = a.o_id
Related Question