Rewrite left outer join involving multiple tables from Informix to Oracle

Question

How do I write an Oracle query which is equivalent to the following Informix query   select tab1 a tab2 b tab3 c tab4 d    from table1 tab1         table2 tab2 OUTER  table3 tab3 table4 tab4 table5 tab5    where tab3 xya   tab4 xya     AND tab4 ss   tab1 ss     AND tab3 dd   tab5 dd     AND tab1 fg   tab2 fg     AND tab4 kk   tab5 kk     AND tab3 desc    XYZ    I tried    select tab1 a tab2 b tab3 c tab4 d    from table1 tab1         table2 tab2 LEFT OUTER JOIN  table3 tab3 table4 tab4 table5 tab5    where tab3 xya   tab4 xya     AND tab4 ss   tab1 ss     AND tab3 dd   tab5 dd     AND tab1 fg   tab2 fg     AND tab4 kk   tab5 kk     AND tab3 desc    XYZ    What is the correct syntax

User · Answer

I m guessing that you want something like  SELECT tab1 a  tab2 b  tab3 c  tab4 d   FROM table1 tab1         JOIN table2 tab2 ON  tab1 fg   tab2 fg         LEFT OUTER JOIN table4 tab4 ON  tab1 ss   tab4 ss         LEFT OUTER JOIN table3 tab3 ON  tab4 xya   tab3 xya and tab3 desc    XYZ          LEFT OUTER JOIN table5 tab5 on  tab4 kk   tab5 kk AND                                        tab3 dd   tab5 dd

User · Answer

Write one table per join  like this   select tab1 a tab2 b tab3 c tab4 d  from    table1 tab1   inner join table2 tab2 on tab2 fg   tab1 fg   left join table3 tab3 on tab3 xxx   tab1 xxx and tab3 desc    XYZ    left join table4 tab4 on tab4 xya   tab3 xya and tab4 ss   tab3 ss   left join table5 tab5 on tab5 dd   tab3 dd and tab5 kk   tab4 kk   Note that while my query contains actual left join  your query apparently doesn t   Since the conditions are in the where  your query should behave like inner joins   Although I admit I don t know Informix  so maybe I m wrong there    The specfific Informix extension used in the question works a bit differently with regards to left joins  Apart from the exact syntax of the join itself  this is mainly in the fact that in Informix  you can specify a list of outer joined tables  These will be left outer joined  and the join conditions can be put in the where clause  Note that this is a specific extension to SQL  Informix also supports  normal  left joins  but you can t combine the two in one query  it seems   In Oracle this extension doesn t exist  and you can t put outer join conditions in the where clause  since the conditions will be executed regardless    So look what happens when you move conditions to the where clause   select tab1 a tab2 b tab3 c tab4 d  from    table1 tab1   inner join table2 tab2 on tab2 fg   tab1 fg   left join table3 tab3 on tab3 xxx   tab1 xxx   left join table4 tab4 on tab4 xya   tab3 xya   left join table5 tab5 on tab5 dd   tab3 dd and tab5 kk   tab4 kk where   tab3 desc    XYZ  and   tab4 ss   tab3 ss   Now  only rows will be returned for which those two conditions are true  They cannot be true when no row is found  so if there is no matching row in table3 and or table4  or if ss is null in either of the two  one of these conditions is going to return false  and no row is returned  This effectively changed your outer join to an inner join  and as such changes the behavior significantly    PS  left join and left outer join are the same  It means that you optionally join the second table to the first  the left one   Rows are returned if there is only data in the  left  part of the join  In Oracle you can also right  outer  join to make not the left  but the right table the leading table  And there is and even full  outer  join to return a row if there is data in either table

[sql] Rewrite left outer join involving multiple tables from Informix to Oracle

Examples related to sql

Examples related to oracle

Examples related to left-join

Examples related to informix

Examples related to outer-join