多列上的 Oracle SQL 外连接 - 错误编码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7287830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL outer join on multiple columns -miscoding
提问by louimat
According to http://www.orafaq.com/node/855The record in that table for "Incomplete Join Trail"
根据 http://www.orafaq.com/node/855该表中的记录为“Incomplete Join Trail”
Wrong way of coding is:
错误的编码方式是：
select *
from T1, T2, T3, T4
where T1.C1 = T2.C1(+)
and T2.C2 = T3.C2(+)
and T3.C3 = T4.C3;
Right way of coding is:
正确的编码方式是：
select *
from T1, T2, T3, T4
where T1.C1 = T2.C1(+)
and T2.C2 = T3.C2(+)
and T3.C3 = T4.C3(+);
This I couldn't understand. There is a possibility that I might want to do a equi-join T3 & T4 and outer join between the other tables. Why is the author saying it is incorrect?
这是我无法理解的。我有可能想要在其他表之间进行等连接 T3 & T4 和外部连接。为什么作者说它不正确？
According to http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5229892958977,
根据http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5229892958977，
Tom is using the following example in his answer which seems similar to the "Incomplete Join Trail" example listed above,
汤姆在他的回答中使用了以下示例，这似乎类似于上面列出的“不完整的连接路径”示例，
select *
from t1,t2,t3
where t1.x = t2.x and t2.y = t3.y(+);
Could anyone help me understand this?
谁能帮我理解这一点？
Edit:
编辑：
Please correct me if I am wrong. As per Allan's answer, the following code would be correct:
如果我错了，请纠正我。根据艾伦的回答，以下代码是正确的：
select *
from T1, T2, T3, T4
where T3.C3 = T4.C3
and T1.C1 = T2.C1(+)
and T2.C2 = T3.C2(+);
If the above is correct, will it be equivalent to the following quoted answer?.[ie., Can we do this in oracle's sql without the subquery in the quoted answer?]
如果以上是正确的，它是否等同于以下引用的答案？.[即，我们可以在oracle的sql中执行此操作而无需引用答案中的子查询吗？]
SELECT *
FROM t1, t2, (SELECT *
FROM t3, t4
WHERE t3.c3 = t4.c3) t3_4
WHERE t1.c1 = t2.c1(+) AND t2.c2 = t3_4.c2(+);
回答by Allan
Inner joins require a matching row to be present in order to return a row. In your first query, rows must be present in tables T1 and T4 in order for a resulting row to be returned. Since T1 connects to T4 by going through T2 and T3, those outer joins are render irrelevant. You can check this pretty easily, as the optimizer will know that they're irrelevant and will show them as inner joins in the explain plan.
内连接需要存在匹配的行才能返回一行。在您的第一个查询中，表 T1 和 T4 中必须存在行才能返回结果行。由于 T1 通过经过 T2 和 T3 连接到 T4，因此这些外部连接变得无关紧要。您可以很容易地检查这一点，因为优化器会知道它们是不相关的，并将它们显示为解释计划中的内部联接。
You can still have an inner join between T3 and T4, but you need to do it in a sub-query in order to make the outer join with T2 functional:
您仍然可以在 T3 和 T4 之间进行内部连接，但您需要在子查询中执行此操作，以使与 T2 的外部连接功能正常：
SELECT *
FROM t1, t2, (SELECT *
FROM t3, t4
WHERE t3.c3 = t4.c3) t3_4
WHERE t1.c1 = t2.c1(+) AND t2.c2 = t3_4.c2(+);
Incidentally, SQL-99 syntax is preferred these days, which would allow you to do this without the sub-query:
顺便提一下，现在 SQL-99 语法是首选，它允许您在没有子查询的情况下执行此操作：
SELECT *
FROM t1
LEFT JOIN t2
ON t1.c1 = t2.c1
LEFT JOIN (t3
JOIN t4
ON t3.c3 = t4.c3)
ON t2.c2 = t3.c2;
Finally, in the example you use from AskTom, the outer join is on the last join in the chain, not the middle. That case is okay.
最后，在您从 AskTom 使用的示例中，外连接位于链中的最后一个连接上，而不是中间连接上。那个案子没问题。
Regarding the revised question, no the new query would not work. The whereclause is not ordered, so the optimizer will treat that in exactly the same way as the original query. As far as I know, there's not any way to resolve this outside of using some form of sub-clause in the fromclause.
关于修改后的问题，没有新的查询不起作用。该where子句没有排序，因此优化器将以与原始查询完全相同的方式处理它。据我所知，除了在from条款中使用某种形式的子条款之外，没有任何方法可以解决这个问题。
