sql - 将涉及多个表的左外连接从 Informix 重写为 Oracle

Question

如何编写与以下 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"

我试过:

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"

什么是正确的语法？

Answer 1

每个连接写一张表，如下所示:

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

请注意，虽然我的查询包含实际的左连接，但您的查询显然没有。
由于条件在 where 中，您的查询应该表现得像内部联接。 (虽然我承认我不了解 Informix，所以也许我错了)。

The specfific Informix extension used in the question works a bit differently关于左连接。除了连接本身的确切语法之外，这主要是因为在 Informix 中，您可以指定外部连接表的列表。这些将被左外连接，连接条件可以放在 where 子句中。请注意，这是 SQL 的特定扩展。 Informix 还支持“普通”左联接，但您似乎无法将两者结合在一个查询中。

在 Oracle 中，此扩展不存在，并且您不能在 where 子句中放置外部连接条件，因为无论如何都会执行条件。

所以看看当你将条件移动到 where 子句时会发生什么:

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

现在，只返回满足这两个条件的行。当没有找到行时，它们不能为真，因此如果 table3 和/或 table4 中没有匹配的行，或者如果 ss在两者中的任何一个中为 null，这些条件之一将返回 false，并且不返回任何行。这有效地将您的外连接更改为内连接，因此显着改变了行为。

PS: left join和 left outer join是一样的。这意味着您可以选择将第二个表连接到第一个(左侧的)。如果连接的“左”部分只有数据，则返回行。在 Oracle 中，您也可以 right [outer] join不是让左表，而是让右表成为主表。甚至还有 full [outer] join如果任一表中有数据，则返回一行。