显式连接与隐式连接?

2021-12-17 00:00:00 join performance database oracle

我的数据库教授告诉我们使用:

My Database Professor told us to use:

SELECT A.a1, B.b1 FROM A, B WHERE A.a2 = B.b2;

而不是:

SELECT A.a1, B.b1 FROM A INNER JOIN B ON A.a2 = B.b2;

据说 Oracle 不喜欢 JOIN 语法，因为这些 JOIN 语法比笛卡尔积的 WHERE 限制更难优化.

Supposedly Oracle don't likes JOIN-Syntaxes, because these JOIN-syntaxes are harder to optimize than the WHERE restriction of the Cartesian Product.

我无法想象为什么会这样.唯一的性能问题可能是解析器需要再解析几个字符.但这在我看来微不足道.

I can't imagine why this should be the case. The only Performance issue could be that the parser Needs to parse a few characters more. But that is negligible in my eyes.

我发现了这个堆栈溢出问题:

I found this Stack Overflow Questions:

  • 是是否有关于使用显式 ANSI JOIN 与隐式连接的 Oracle 官方建议?
  • 显式与隐式 SQL 连接

Oracle 文档中的这句话:https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

And this sentence in a Oracle Documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

Oracle 建议您使用 FROM 子句 OUTER JOIN 语法而不是 Oracle 连接运算符.

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator.

有人可以通过链接向我提供来自 Oracle 的最新建议吗?因为她不承认 StackOverflow(这里可以回答每个人)并且 10g 文档在这里眼中已经过时了.

Can someone give me up-to-date recommendations from Oracle with link. Because she don't acknowledges StackOverflow (here can answer everyone) and the 10g Documentation is outdated in here eyes.

如果我错了，而且 Oracle 现在真的不喜欢 JOINS，那也没关系，但我找不到文章.我只想知道谁是对的.

If i am wrong and Oracle realy don't likes JOINS now than thats also ok, but i don't find articles. I just want to know who is Right.

非常感谢所有能帮助我的人！

Thanks a lot to everyone who can help me!

推荐答案

您的教授应该与哥伦比亚大学计算机科学教授 Gordon Linoff 交谈.Gordon 和该站点上的大多数 SQL 爱好者几乎总是会告诉您使用显式连接语法.造成这种情况的原因很多，包括(但不限于):

Your professor should speak with Gordon Linoff, who is a computer science professor at Columbia University. Gordon, and most SQL enthusiasts on this site, will almost always tell you to use explicit join syntax. The reasons for this are many, including (but not limited to):

  • 显式连接可以很容易地看出实际的连接逻辑是什么.另一方面，隐式连接通过将连接逻辑分散到 FROM 和 WHERE 子句中来混淆连接逻辑.
  • ANSI 92 标准建议使用现代显式连接，实际上弃用了您的教授似乎正在推动的隐式连接
  • Explicit joins make it easy to see what the actual join logic is. Implicit joins, on the other hand, obfuscate the join logic, by spreading it out across both the FROM and WHERE clauses.
  • The ANSI 92 standard recommends using modern explicit joins, and in fact deprecated the implicit join which your professor seems to be pushing

关于性能，据我所知，您编写的查询的两个版本都将在后台优化为相同的内容.您可以随时检查两者的执行计划，但我怀疑您经常会看到显着差异.

Regarding performance, as far as I know, both versions of the query you wrote would be optimized to the same thing under the hood. You can always check the execution plans of both, but I doubt you would see a significant difference very often.

相关文章