SQL 甲骨文“(+)”运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4020786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle "(+)" Operator
提问by Sekhar
I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.
我正在检查一些旧的 SQL 语句，以便记录它们并可能增强它们。
The DBMS is Oracle
DBMS 是 Oracle
I did not understand a statement which read like this:
我不明白这样的陈述：
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).
我对(+)运营商感到困惑，无法在任何论坛上获得它......（在引号内搜索 + 也不起作用）。
Anyway, I used 'Explain Plan' of SQLDeveloper and I got an output saying that HASH JOIN, RIGHT OUTER, etc.
无论如何，我使用了 SQLDeveloper 的“解释计划”，我得到了一个输出，说HASH JOIN, RIGHT OUTER等等。
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.
如果我(+)在查询结束时删除运算符会有什么不同吗？数据库是否必须满足某些条件（例如具有某些索引等）才能(+)使用？如果您能给我一个简单的理解，或者一些我可以阅读的好链接，那将非常有帮助。
Thanks!
谢谢！
回答by OMG Ponies
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.
这是 OUTER JOIN 的 Oracle 特定表示法，因为 ANSI-89 格式（在 FROM 子句中使用逗号分隔表引用）没有标准化 OUTER 连接。
The query would be re-written in ANSI-92 syntax as:
查询将以 ANSI-92 语法重写为：
SELECT ...
FROM a
LEFT JOIN b ON b.id = a.id
This link is pretty good at explaining the difference between JOINs.
It should also be noted that even though the (+)works, Oracle recommends notusing it:
还应该注意的是，即使(+)有效，Oracle 也建议不要使用它：
Oracle recommends that you use the
FROMclauseOUTER JOINsyntax 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 theFROMclauseOUTER JOINsyntax:
Oracle 建议您使用
FROM子句OUTER JOIN语法而不是 Oracle 连接运算符。使用 Oracle 连接运算符的外连接查询(+)受以下规则和限制的约束，这些规则和限制不适用于FROM子句OUTER JOIN语法：
回答by hot dog
The (+) operator indicates an outer join. This means that Oracle will still return records from the other side of the join even when there is no match. For example if a and b are emp and dept and you can have employees unassigned to a department then the following statement will return details of all employees whether or not they've been assigned to a department.
(+) 运算符表示外连接。这意味着即使没有匹配项，Oracle 仍会从连接的另一端返回记录。例如，如果 a 和 b 是 emp 和 dept，并且您可以将员工未分配到某个部门，那么以下语句将返回所有员工的详细信息，无论他们是否已分配到某个部门。
select * from emp, dept where emp.dept_id=dept.dept_id(+)
So in short, removing the (+) may make a significance difference but you might not notice for a while depending on your data!
所以简而言之，删除 (+) 可能会产生显着差异，但根据您的数据，您可能暂时不会注意到！
回答by Md. Salman Fahad Famous
In Oracle, (+) denotes the "optional" table in the JOIN. So in your query,
在 Oracle 中，(+) 表示 JOIN 中的“可选”表。所以在你的查询中，
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id(+)
it's a LEFT OUTER JOIN of table 'b' to table 'a'. It will return all data of table 'a' without losing its data when the other side (optional table 'b') has no data.
它是表 'b' 到表 'a' 的 LEFT OUTER JOIN。当另一方（可选表'b'）没有数据时，它将返回表'a'的所有数据而不会丢失其数据。
The modern standard syntax for the same query would be
相同查询的现代标准语法是
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b ON a.id=b.id
or with a shorthand for a.id=b.id(not supported by all databases):
或使用简写a.id=b.id（并非所有数据库都支持）：
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b USING(id)
If you remove (+) then it will be normal inner join query
如果删除 (+) 那么它将是正常的内部连接查询
Older syntax, in both Oracle and other databases:
Oracle 和其他数据库中的旧语法：
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id
More modern syntax:
更现代的语法：
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
INNER JOIN b ON a.id=b.id
Or simply:
或者干脆：
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
JOIN b ON a.id=b.id
It will only return all data where both 'a' & 'b' tables 'id' value is same, means common part.
它只会返回'a'和'b'表'id'值相同的所有数据，意味着公共部分。
If you want to make your query a Right Join
如果您想让您的查询成为右连接
This is just the same as a LEFT JOIN, but switches which table is optional.
这与 LEFT JOIN 相同，但切换哪个表是可选的。
Old Oracle syntax:
旧的 Oracle 语法：
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id(+)=b.id
Modern standard syntax:
现代标准语法：
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
RIGHT JOIN b ON a.id=b.id
Ref & help:
参考与帮助：
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187
https://asktom.oracle.com/pls/asktom/f?p=100:11::::P11_QUESTION_ID:6585774577187
Left Outer Join using + sign in Oracle 11g
回答by sunny
In practice, the + symbol is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or null values within the conditional).
在实践中，+ 符号直接放置在条件语句中和可选表的一侧（允许在条件中包含空值或空值）。
