是否有Oracle官方建议使用显式ANSI JOIN与隐式联接? [英] Is there an Oracle official recommendation on the use of explicit ANSI JOINs vs implicit joins?

问题描述

注意:我不是要您告诉我使用显式联接"，而是要寻找Oracle在该主题上的正式职位.

摘自Oracle数据库文档(也出现在9i和11g文档中):

From Oracle database documentation (also appears in 9i and 11g documentations):

Oracle建议您使用FROM子句OUTER JOIN语法 而不是Oracle联接运算符.使用 Oracle联接运算符(+)遵循以下规则，并且 限制[…]

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions […]

换句话说，Oracle建议首选以下两种形式中的第一种:

In other words, Oracle advises to prefer the first of these two forms:

FROM a LEFT JOIN b ON b.x = a.x
vs
FROM a, b WHERE b.x(+) = a.x

但是，我从未在任何Oracle文档中找到建议最好使用以下两种形式之一的建议:

However, I have never found in any Oracle documentation a single recommendation to use preferably one of those two forms:

FROM a INNER JOIN b ON b.x = a.x
vs
FROM a, b WHERE b.x = a.x

我错过了一段吗?

推荐答案

如果有，我还没有看到.对于外部联接特别喜欢ANSI语法的原因(除了非标准的，Oracle特定的(+)符号之外)是，使用ANSI语法可表示更多的外部联接.限制"ORA-01417:一个表最多可以与另一个表进行外部联接"适用于(+)外部联接，但不适用于ANSI外部联接.不适用于ANSI外部联接的其他(+)限制是

I haven't seen it if there is. The reason for preferring ANSI syntax for outer joins in particular (apart from the non-standarrd, Oracle-specific (+) symbol) is that more outer joins are expressible using the ANSI syntax. The restriction "ORA-01417: a table may be outer joined to at most one other table" applies to (+) outer joins but not to ANSI outer joins. Other restrictions on (+) that do not apply to ANSI outer joins are documented here.

一位受人尊敬的Oracle专家实际上建议对内部联接使用旧语法-请参见

One highly respected Oracle expert actually recommends sticking to the old syntax for inner joins - see Jonathan Lewis's blog. He says there that ANSI joins are transformed to traditional Oracle joins under the covers anyway. I don't agree with him 100% (I prefer ANSI joins myself in general), but would not claim to have a fraction of his knowledge on the topic.

简而言之，ANSI外连接在技术上优于旧的(+)连接，而对于内连接则仅是样式问题.

In a nutshell, ANSI outer joins are technically superior to old (+) joins, whereas with inner joins it is more just a matter of style.

这篇关于是否有Oracle官方建议使用显式ANSI JOIN与隐式联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！