-1

I have Oracle sql code that uses the old and not recommended (+) notation. I have a basic understanding of how to convert but am struggling with multiple tables.

For exmaple

select [list of columns]
from table1 t1,
     table2 t2,
     table3 t3,
     table4 t4,
     table5 t5
where t2.col1 = t1.col1
and t2.col2 = t3.col2
and t4.col1(+) = t2.col3
and t5.col1(+) = t2.col4
and t5.col2(+) = t2.col5

Hope this makes sense. I believe this is slightly different from other similar questions as they did not cover multiple joins in the same query

Paul Wilson
  • 17
  • 3

2 Answers2

0
t4.col1(+) = t2.col3

would convert to

RIGHT OUTER JOIN table2 t2 on t4.col1 = t2.col3

etc.

but since table2 should be joined to table1 first, it would be cleaner to use the more common LEFT join and flip the tables:

from table1 t1,
INNER JOIN table2 t2 ON t2.col1 = t1.col1
LEFT JOIN table3 t3 ON t2.col2 = t3.col2
LEFT JOIN table4 t4 ON t2.col3 = t4.col1
LEFT JOIN table5 t5 ON t2.col4 = t5.col1
    and t2.col5 = t5.col2
D Stanley
  • 149,601
  • 11
  • 178
  • 240
0

Just move each join condition from the WHERE clause into the corresponding JOIN:

select [list of columns]
from   table1 t1
       join table2 t2
            on  t2.col1 = t1.col1
       join table3 t3
            on  t3.col2 = t2.col2
       left join table4 t4
            on  t4.col1 = t2.col3
       left join table5 t5
            on  t5.col1 = t2.col4
            and t5.col2 = t2.col5

I never use the optional (i.e. redundant) inner and outer keywords, or right join (since you can always just reverse it to make a normal left join and avoid a lot of confusion).

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • Hehe, "optional" and "redundant" aren't synonyms. You seem to also imply "unnecessary" - which is a matter of opinion. Whenever you can make your meaning crystal clear, you may or may not prefer to do so. I do. Leave no room to confusion! (For example, for people who may come from other DB products where one or another of these keywords may be required.) –  Jan 03 '18 at 17:26