# Converting multiple SQL implicit joins into explicit joins

• 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
``````
-
You said you tried Googling, but this is pretty basic, so I don't think you really did, or you just want us to write your code for you. – siride May 13 '14 at 2:57
Hi @siride, as mentioned I am looking for example/guides/formal specs on how to resolve this. Consider: Line 3,4: `c` LJ `h`; Line 5,6: `h` LJ `g` - now how do I combine these two..? `c` LJ `h` <LJ/RJ/IJ> `h` LJ `g` - doesn't seem right.. – Kent Pawar May 13 '14 at 3:23

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
``````
-
Hi @Ted, really appreciate your explanation! Need a few mins to understand how the intermediate joins work.. – Kent Pawar May 13 '14 at 3:25
I have updated by question with my understanding of your algorithm.. It's too long to read :). Here is where I am stuck - for "`c.c_id = h.c_id(+)`" => `c LEFT JOIN h`. But above we are not getting all the records of `c` to participate in the join with `h`, ..correct? – Kent Pawar May 13 '14 at 5:29
Hi Ted, could you kindly help me understand your steps 9,10 that I have updated in my question..? Thanks – Kent Pawar May 28 '14 at 19:41
According to your original query you should not be getting all records of `c` to participate in the join with `h`. You have 2 other conditions for `c`, `c.p_id = a.p_id` and `d.c_id = c.c_id`, so the records from `c` must match those conditions and only those records that match the conditions would participate in the join with `h`. Similar logic goes for join between `g` and `h`. – Ted Elliott May 31 '14 at 2:20