在Oracle中的多个表上进行左外连接

How to write a oracle query which is equivalent to the below query in informix:

如何在informix中编写一个等同于以下查询的oracle查询：

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 as:

我试过：

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"

Can anyone help me to get the correct syntax.

任何人都可以帮助我获得正确的语法。

2 个解决方案

#1

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
  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
where
  tab3.desc = "XYZ"

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).

请注意，虽然我的查询包含实际的左连接，但您的查询显然没有。由于条件在where，您的查询应该像内部联接一样。（虽然我承认我不知道Informix，所以也许我错了）。

If that is indeed the case, you can change left join to inner join. You should use inner join where possible, since they are faster than left join.

如果确实如此，您可以将左连接更改为内连接。您应尽可能使用内连接，因为它们比左连接更快。

PS: Left join and left outer join are the same.

PS：左连接和左外连接是相同的。

#2

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)

#1