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

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

注意:我不是要您告诉我使用显式连接",而是要查找 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 专家实际上建议坚持使用旧语法进行内部联接 - 请参阅 乔纳森刘易斯的博客.他在那里说 ANSI 连接在幕后转换为传统的 Oracle 连接.我不 100% 同意他的观点(我一般更喜欢 ANSI 加入我自己),但不会声称他对该主题的了解只有一小部分.

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.

相关文章