SQL 在 Oracle 中连接表（多个外连接）

Joining tables in Oracle (multiple outer joins)

提问by divya chekuri

I am trying to convert an Informix query to Oracle:

我正在尝试将 Informix 查询转换为 Oracle：

The Informix query looks like this:

Informix 查询如下所示：

SELECT
    r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr 
FROM
    tab1 u, tab2 ud, 
OUTER (tab3 a, tab4 n, tab5 nd, tab6 r, OUTER (tab7 g, tab8 atr))
WHERE
    r.xx = n.xx AND
    n.nas = a.nas AND
    a.user = u.user AND
    a.ac = g.ac AND
    n.nas1 = nd.nas1 AND
    u.user1 = ud.user1 AND
    atr.sso = g.sso AND
    UPPER(atr.name) = 'NAME' AND
    u.id = 102 

The Oracle query looks like this:

Oracle 查询如下所示：

SELECT
    r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr
FROM
    tab1 u
INNER JOIN tab2 ud ON
    u.user1 = ud.user1 AND
    u.id = 102
LEFT OUTER JOIN tab3 a ON a.user = u.user 
LEFT OUTER JOIN tab4 n ON n.nas = a.nas
LEFT OUTER JOIN tab5 nd ON  n.nas1 = nd.nas1 
LEFT OUTER JOIN tab6 r ON r.xx = n.xx

I am not sure how to join the other two tables.

我不确定如何加入其他两个表。

Can any one help me?

谁能帮我？

回答by Jonathan Leffler

I believe the query should look something like this:

我相信查询应该是这样的：

SELECT r.aa, n.bb, nd.cc, u.id, ud.dd, g.attr
  FROM            tab1 AS u
       INNER JOIN tab2 AS v ON u.user1 = v.user1 AND u.id = 102
  LEFT OUTER JOIN tab3 AS a ON a.user  = u.user 
  LEFT OUTER JOIN tab4 AS n ON n.nas   = a.nas
  LEFT OUTER JOIN tab5 AS d ON n.nas1  = d.nas1 
  LEFT OUTER JOIN tab6 AS r ON r.xx    = n.xx
  LEFT OUTER JOIN (SELECT g.attr, g.ac
                     FROM tab7 AS x
                     JOIN tab8 AS atr ON x.sso = atr.sso
                    WHERE UPPER(atr.name) = 'NAME'
                  )    AS g ON a.ac    = g.ac

I changed the alias 'nd' to just 'd' and 'ud' to 'v' so that all aliases are single-letters. The nested OUTER(tab7 g, tab8 atr)in the Informix notation is itself an inner join (as in the sub-select in my version), but that result set is outer joined with a.ac. This is what the rewrite says.

我将别名“nd”更改为“d”，将“ud”更改为“v”，以便所有别名都是单字母。OUTER(tab7 g, tab8 atr)Informix 表示法中的嵌套本身是一个内连接（如我版本中的子选择），但该结果集是与a.ac. 重写是这么说的。

I used a WHERE clause in the sub-query; the WHERE condition could be left in the ON clause if you preferred. The chances are the optimizer will handle both correctly and equivalently. Similarly, the AND u.id = 102in the inner join could be placed into a WHERE clause. Again, the optimizer would probably push the filter condition down for better performance.

我在子查询中使用了 WHERE 子句；如果您愿意，可以将 WHERE 条件留在 ON 子句中。优化器可能会正确且等效地处理。类似地，AND u.id = 102内连接中的 可以放入 WHERE 子句中。同样，优化器可能会降低过滤条件以获得更好的性能。

Note that the UPPER function in the sub-query likely requires a table-scan - unless you have a functional index on UPPER(atr.name).

请注意，子查询中的 UPPER 函数可能需要表扫描 - 除非您在UPPER(atr.name).

---

---

Revisiting this, the transliteration of the initial part of the query is not accurate.

重新审视这一点，查询的初始部分的音译是不准确的。

The original query included the FROM clause:

原始查询包括 FROM 子句：

FROM tab1 u, tab2 ud, OUTER(tab3 a, tab4 n, tab5 nd, tab6 r, OUTER(tab7 g, tab8 atr))

The tables tab3, tab4, tab5and tab6are inner-joined to each other, and the result is outer-joined to tab1and tab2. Similarly, tab8is inner-joined to tab7, but the result of that is outer-joined to the inner-join of tables 3-6. The original answer I gave (based on the outline answer in the question) would be represented in the old Informix notation using:

表tab3，tab4，tab5和tab6是内彼此接合，其结果是外连结到tab1和tab2。类似地，tab8内连接到tab7，但其结果外连接到表 3-6 的内连接。我给出的原始答案（基于问题中的大纲答案）将使用旧的 Informix 表示法表示：

FROM tab1 u, tab2 ud,
     OUTER(tab3 a, OUTER(tab4 n, OUTER(tab5 nd, OUTER(tab6 r, OUTER(tab7 g, tab8 atr)))))

Thus, it would be more accurate to transcribe the original query as:

因此，将原始查询转录为：

SELECT r.aa, n.bb, nd.cc, u.id, ud.dd, g.attr
  FROM tab1 AS u
  JOIN tab2 AS v ON u.user1 = v.user1 AND u.id = 102
  LEFT OUTER JOIN
       (SELECT *
          FROM tab3 AS a ON a.user  = u.user 
          JOIN tab4 AS n ON n.nas   = a.nas
          JOIN tab5 AS d ON n.nas1  = d.nas1 
          JOIN tab6 AS r ON r.xx    = n.xx
          LEFT OUTER JOIN
               (SELECT g.attr, g.ac
                 FROM tab7 AS x
                 JOIN tab8 AS atr ON x.sso = atr.sso
                WHERE UPPER(atr.name) = 'NAME'
               )    AS g ON a.ac    = g.ac
       ) AS loj

The residual problem would be ensuring that the correct aliases are in use for the columns from the complex lojsub-query. Note that in the absence of LEFT, RIGHT or FULL, a JOIN is assumed to be an INNER join; also, if you specify LEFT, RIGHT or FULL, the OUTER is optional.

剩余的问题是确保复杂loj子查询中的列使用正确的别名。请注意，在没有 LEFT、RIGHT 或 FULL 的情况下，JOIN 被假定为 INNER 连接；此外，如果您指定 LEFT、RIGHT 或 FULL，则 OUTER 是可选的。

One other detail to note: the behaviour of the old-style Informix OUTER join under filter conditions is not the same as the behaviour of Standard SQL OUTER joins. This seldom makes a difference, but it could occasionally be important. On the whole, the behaviour of Standard SQL OUTER joins is more usually what you want, but you if you run regression tests and find that there's a difference in the answers, the explanation might be that the old-style Informix OUTER join does things differently from the new-style Standard SQL OUTER joins.

另一个需要注意的细节：旧式 Informix OUTER 联接在过滤条件下的行为与标准 SQL OUTER 联接的行为不同。这很少有什么不同，但有时可能很重要。总的来说，标准 SQL OUTER 联接的行为更通常是您想要的，但是如果您运行回归测试并发现答案存在差异，则解释可能是旧式 Informix OUTER 联接的处理方式不同来自新型标准 SQL OUTER 连接。

回答by Lost in Alabama

I would try adding these:

我会尝试添加这些：

LEFT OUTER JOIN tab7 g ON a.ac = g.ac
LEFT OUTER JOIN tab8 atr ON g.sso = atr.sso AND UPPER(atr.name) = 'NAME'