SQL Oracle：“(+)”在 WHERE 子句中有什么作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/430274/
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: What does `(+)` do in a WHERE clause?
提问by Jonathan Lonowski
Found the following in an Oracle-based application that we're migrating (generalized):
在我们正在迁移（通用）的基于 Oracle 的应用程序中发现以下内容：
SELECT
Table1.Category1,
Table1.Category2,
count(*) as Total,
count(Tab2.Stat) AS Stat
FROM Table1, Table2
WHERE (Table1.PrimaryKey = Table2.ForeignKey(+))
GROUP BY Table1.Category1, Table1.Category2
What does (+)do in a WHERE clause? I've never seen it used like that before.
是什么(+)在WHERE子句中呢？我以前从未见过这样使用它。
回答by SquareCog
Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by people who learned it first, since they like that it makes their code shorter.
根据“=”的哪一侧，“(+) 表示 LEFT OUTER 或 RIGHT OUTER 连接（在这种情况下，它是左外部连接）。这是旧的 Oracle 语法，有时会被一些人喜欢首先学习它，因为他们喜欢它使他们的代码更短。
Best not to use it though, for readability's sake.
不过，为了可读性，最好不要使用它。
回答by Bill Karwin
As others have stated, the (+)syntax is obsolete, proprietary syntax that Oracle used for years to accomplish the same results as an OUTER JOIN. I assume they adopted their proprietary syntax before SQL-92 decided on the standard syntax.
正如其他人所说，该(+)语法是过时的专有语法，Oracle 多年来一直使用它来实现与OUTER JOIN. 我假设他们在 SQL-92 决定标准语法之前采用了他们的专有语法。
The equivalent query to the one you showed, using standard SQL OUTER JOINsyntax (which is now supported by all major RDBMS implementations) would be the following:
使用标准 SQLOUTER JOIN语法（现在所有主要 RDBMS 实现都支持）的等效查询如下所示：
SELECT
Table1.Category1,
Table1.Category2,
COUNT(*) AS Total,
COUNT(Table2.Stat) AS Stat
FROM Table1
LEFT OUTER JOIN Table2 ON (Table1.PrimaryKey = Table2.ForeignKey)
GROUP BY Table1.Category1, Table1.Category2;
Which means:
意思是：
- All rows from
Table1are included in the query result. - Where there are matching rows in
Table2, include those rows (repeating content fromTable1if there are multiple matching rows inTable2). - Where there are no matching rows in
Table2, useNULLfor all ofTable2's columns in the query result.
- 来自的所有行都
Table1包含在查询结果中。 - 如果 中有匹配的行
Table2，则包括这些行（Table1如果 中有多个匹配的行，则重复内容Table2）。 - 如果 中没有匹配的行
Table2，则NULL用于Table2查询结果中的所有's 列。
回答by Otávio Décio
It's a non ANSI left outer join notation. Starting with Oracle9i, the confusing outer join syntax using the ‘(+)' notation has been superseded by ISO 99 outer join syntax.
这是一个非 ANSI 左外连接符号。从 Oracle9i 开始，使用“(+)”符号的令人困惑的外连接语法已被 ISO 99 外连接语法取代。
回答by sachin mathur
A noteworthy consideration is that the classic Oracle notation is not intuitive and is best avoided from a code clarity and maintainability perspective.
一个值得注意的考虑是经典的 Oracle 符号不直观，最好从代码清晰度和可维护性的角度避免。
To illustrate this point, I have included this example.
为了说明这一点，我已经包含了这个例子。
To achieve a LEFT outer joinbetween tables Aand Bone would expect the table on the left which is A should have the (+) operator next to it. This would make sense as we want to denote we would include all rows of A regardless of the success in join criteria with B. However this is not the case and the join is achieved as follows
要实现LEFT outer join表之间的关系A，B人们会期望左侧的表 A 旁边应该有 (+) 运算符。这是有道理的，因为我们想表示我们将包括 A 的所有行，而不管与 B 的连接标准是否成功。 然而事实并非如此，连接实现如下
select b.age, a.name
from Employees a, EmployeeUNI b
where a.id = b.id(+)
I prefer the ANSI SQL version which is explicit:
我更喜欢明确的 ANSI SQL 版本：
select b.age, a.name
From Employees a
LEFT outer join EmployeeUNI b
on a.id = b.id
Both methods result in the same output however the ANSI approach does not come with the risk of the novice programmer mistakenly putting the (+) in the wrong place.
两种方法都会产生相同的输出，但是 ANSI 方法不会带来新手程序员错误地将 (+) 放在错误位置的风险。
