SQL 从 Informix 到 Oracle 重写涉及多个表的左外连接

Rewrite left outer join involving multiple tables from Informix to Oracle

提问by divya chekuri

How do I write an Oracle query which is equivalent to the following Informix query?

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

我试过：

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?

什么是正确的语法？

回答by GolezTrol

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

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

The specfific Informix extension used in the question works a bit differentlywith 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.

问题中使用的特定 Informix 扩展在左连接方面的工作方式略有不同。除了连接本身的确切语法之外，这主要是因为在 Informix 中，您可以指定外部连接表的列表。这些将被左外连接，连接条件可以放在 where 子句中。请注意，这是 SQL 的特定扩展。Informix 还支持“普通”左联接，但您似乎无法将两者结合在一个查询中。

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.

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

So look what happens when you move conditions to the where clause:

所以看看当你将条件移动到 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

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

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

PS: left joinand left outer joinare 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] jointo make not the left, but the right table the leading table. And there is and even full [outer] jointo return a row if there is data in either table.

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

回答by Justin Cave

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)