Oracle“(+)"操作员 [英] Oracle "(+)" Operator

问题描述

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

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

DBMS是Oracle

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.

谢谢！

推荐答案

这是OUTER JOIN的Oracle专用表示法，因为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 join运算符.使用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:

这篇关于Oracle“(+)"操作员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！