oracle ANSI 和非 ANSI 连接有什么区别，您推荐哪种？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19905028/
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
What is difference between ANSI and non-ANSI joins, and which do you recommend?
提问by Sai
I have come across many websites to find the answer about which one is better, ANSI or non- ANSI syntax. What is the difference between these two queries?
我访问了许多网站以找到关于哪一个更好的答案，ANSI 或非 ANSI 语法。这两个查询有什么区别？
select a.name,a.empno,b.loc
from tab a, tab b
where a.deptno=b.deptno(+);
and:
和：
select a.name,a.empno,b.loc
from tab a
left outer join tab b on a.deptno=b.deptno;
The result is same in both the cases. The second query is also longer. Which one is better?
两种情况下的结果相同。第二个查询也更长。哪一个更好？
suppose if we have added another table Salgrade in the above query based on what conditions we need to join them?? .. can any one assume one table and give me explanation
假设我们在上面的查询中添加了另一个表 Salgrade，基于我们需要加入它们的条件？.. 任何人都可以假设一张桌子并给我解释
回答by mucio
both syntaxes usually work without problems, but if you try to add a where condition you will see that with the second one is much simpler to understand which is the join condition and which is the where clause.
这两种语法通常都可以正常工作，但是如果您尝试添加一个 where 条件，您会发现使用第二个语法更容易理解哪个是连接条件，哪个是 where 子句。
1)
1)
SELECT a.name,
a.empno,
b.loc
FROM tab a,
tab b
WHERE a.deptno = b.deptno(+)
AND a.empno = 190;
2)
2)
SELECT a.name,
a.empno,
b.loc
FROM tab a,
LEFT OUTER JOIN tab b
ON a.deptno = b.deptno
WHERE a.empno = 190;
Also, it's much easier to recognize an outer join and do not forget to include the (+). Overall you can say it's just a question of taste, but the truth is that the second syntax is much more readable and less prone to errors.
此外，识别外连接要容易得多，不要忘记包含 (+)。总的来说，您可以说这只是一个品味问题，但事实是第二种语法更具可读性且不易出错。
回答by jpw
The first is a legacy Oracle specific way of writing joins, the second is ANSI SQL-92+ standard and is the preferred one.
第一个是传统的 Oracle 特定的连接编写方式，第二个是 ANSI SQL-92+ 标准并且是首选的。
回答by SriniV
Extensively discussed many a times, including one by me.
广泛讨论了很多次，包括我一个。
Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins.
使用显式 JOIN 而不是隐式（无论它们是否是外连接），因为使用隐式连接意外创建笛卡尔积要容易得多。
With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
使用显式 JOIN，您不能“意外”创建一个。涉及的表越多，您错过一个连接条件的风险就越高。
Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
与 ANSI 连接相比，基本上 (+) 受到严重限制。此外，它仅在 Oracle 中可用，而所有主要 DBMS 都支持 ANSI 连接语法
SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
迁移到 ANSI 语法后，SQL 不会开始表现得更好——它只是不同的语法。
Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
Oracle 强烈建议您使用前面示例中显示的更灵活的 FROM 子句连接语法。过去，ANSI 语法存在一些错误，但如果您使用最新的 11.2 或 12.1，应该已经修复了。
Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
使用 JOIN 运算符可确保您的 SQL 代码符合 ANSI，从而允许前端应用程序更轻松地移植到其他数据库平台。
Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
联接条件对每个表的选择性非常低，而对理论叉积中的元组具有很高的选择性。where 语句中的条件通常具有更高的选择性。
Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.
Oracle 在内部将 ANSI 语法转换为 (+) 语法，您可以在执行计划的 Predicate Information 部分看到这种情况。
If you are using 11.2 I advise ANSI join. If you use 12C, there are some new bugs unearthed on OUTER JOINS.
如果您使用的是 11.2，我建议您使用 ANSI 加入。如果使用12C，在OUTER JOINS上会发现一些新的错误。
I also remember some bugs in Oracle while using ANSI syntax, before 11.2 where it got fixed in 11.2.
我还记得在使用 ANSI 语法时 Oracle 中的一些错误，在 11.2 之前，它在 11.2 中得到修复。
In my opinion, I am not a big fan of ANSI syntax, though Oracle does confirm to the standards of ANSI, it is not totally bug free.
在我看来，我不是 ANSI 语法的忠实粉丝，尽管 Oracle 确实确认了 ANSI 的标准，但它并非完全没有错误。
