甲骨文“(+)"操作员

2021-12-01 00:00:00 join sql oracle outer-join

我正在检查一些旧的 SQL 语句，以便记录它们并可能增强它们.

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.

数据库管理系统是甲骨文

The DBMS is Oracle

我不明白这样的语句:

select ...
from a,b
where a.id=b.id(+)

我对 (+) 运算符感到困惑，并且无法在任何论坛上找到它...(在引号中搜索 + 也不起作用).

I am confused about the (+) operator, and could not get it at any forums... (searching for + within quotes didn't work either).

无论如何，我使用了 SQLDeveloper 的解释计划"，我得到一个输出，说 HASH JOIN、RIGHT OUTER 等

Anyway, I used 'Explain Plan' of SQLDeveloper and I got an output saying that HASH JOIN, RIGHT OUTER, etc.

如果删除查询末尾的 (+) 操作符，会有什么不同吗?在(+) 使用之前，数据库是否必须满足某些条件(比如有一些索引等)??如果您能给我一个简单的理解，或者一些很好的链接，我可以在其中阅读相关内容，这将非常有帮助.

Would there be any difference if I remove the (+) operator at the end of the query? Does the database have to satisfy some condition (like having some indexes, etc.) before (+) can be used?? It would be greatly helpful if you can provide me a simple understanding, or some good links where I can read about this.

谢谢！

推荐答案

这是 Oracle 特定的 OUTER JOIN 表示法，因为 ANSI-89 格式(在 FROM 子句中使用逗号分隔表引用)没有标准化 OUTER加入.

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

查询将以 ANSI-92 语法重写为:

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

这个链接还不错解释 JOIN 之间的区别.

还应该注意的是，即使 (+) 有效，Oracle 建议不要使用它:

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle 建议您使用 FROM 子句 OUTER JOIN 语法而不是 Oracle 连接运算符.使用 Oracle 连接运算符 (+) 的外连接查询受以下规则和限制的约束，这些规则和限制不适用于 FROM 子句 OUTER JOIN语法:

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, which do not apply to the FROM clause OUTER JOIN syntax:

相关文章