Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • 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
share|improve this question
    
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 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 at 3:23
add comment

1 Answer 1

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
share|improve this answer
    
Hi @Ted, really appreciate your explanation! Need a few mins to understand how the intermediate joins work.. –  Kent Pawar May 13 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 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 at 19:41
1  
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 at 2:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.