SQL Oracle 的加号 (+) 表示法和 ansi JOIN 表示法之间的区别？

Difference between Oracle's plus (+) notation and ansi JOIN notation?

提问by Franz See

What's the difference between using oracle's plus notation (+)over the ansi standard joinnotation?

使用 oracle 的加号表示法(+)与 ansi 标准join表示法有什么区别？

Is there a difference in performance?

性能上有区别吗？

Is the plus notation deprecated?

加号被弃用了吗？

回答by nagul

AFAIK, the (+)notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins was put in place. It's specific to Oracle and you should avoid using it in new code when there's an equivalent standards-compliant version available.

AFAIK，该(+)符号仅用于向后兼容，因为 Oracle 在连接的 ANSI 标准实施之前就推出了它。它特定于 Oracle，当有等效的符合标准的版本可用时，您应该避免在新代码中使用它。

It seems there are differences between the two, and the (+)notation has restrictions that the ANSI join syntax does not have. Oracle themselves recommend that you not use the (+)notation. Full description here in the Oracle? Database SQL Language Reference 11g Release 1 (11.1):

两者之间似乎存在差异，并且(+)表示法具有 ANSI 连接语法所没有的限制。Oracle 自己建议您不要使用该(+)表示法。Oracle中的完整说明在这里？数据库 SQL 语言参考 11g 第 1 版 (11.1)：

Oracle recommends that you use the FROMclause OUTER 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 the FROMclause OUTER JOINsyntax:

• You cannot specify the (+)operator in a query block that also contains FROMclause join syntax.
• The (+)operator can appear only in the WHEREclause or, in the context of left-correlation (when specifying the TABLEclause) in the FROMclause, and can be applied only to a column of a table or view.
• If A and B are joined by multiple join conditions, then you must use the (+)operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
• The (+)operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
• You cannot use the (+)operator to outer-join a table to itself, although self joins are valid.

For example, the following statement is not valid:

SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;

However, the following self join is valid:

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;

• The (+)operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+)operator.
• A WHEREcondition containing the (+)operator cannot be combined with another condition using the ORlogical operator.
• A WHEREcondition cannot use the INcomparison condition to compare a column marked with the (+)operator with an expression.

If the WHEREclause contains a condition that compares a column from table B with a constant, then the (+)operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+)operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECTfor the syntax for an outer join.

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

• 您不能(+)在还包含FROM子句连接语法的查询块中指定运算符。
• 的(+)操作者可以只出现在WHERE条款，或在左相关的上下文中（指定时TABLE的条款）FROM条款，并且只能被应用到的表或视图的一列。
• 如果 A 和 B 由多个连接条件连接，则您必须(+)在所有这些条件中使用运算符。如果不这样做，则 Oracle 数据库将仅返回由简单联接产生的行，但不会出现警告或错误提示您没有外部联接的结果。
• 的(+)，如果你在一个内部查询外部查询和其他表指定一个表操作不会产生一个外部联接。
• (+)尽管自联接是有效的，但您不能使用运算符将表与自身进行外联接。

例如，以下语句无效：

SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;

但是，以下自联接是有效的：

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;

• 该(+)运算符只能应用于列，不能应用于任意表达式。但是，任意表达式可以包含一个或多个用(+)运算符标记的列。
• WHERE包含(+)运算符的条件不能与使用OR逻辑运算符的另一个条件组合。
• 甲WHERE条件不能使用IN比较条件，比较标有一列(+)带表达式运算符。

如果该WHERE子句包含将表 B 中的列与常量进行比较的条件，则(+)必须将该运算符应用于该列，以便 Oracle 返回表 A 中已为该列生成空值的行。否则 Oracle 只返回简单连接的结果。

在执行两对以上表的外连接的查询中，单个表可以是仅另一个表的空生成表。因此，不能将(+)运算符应用于 A 和 B 的连接条件以及 B 和 C 的连接条件中的 B 列。SELECT有关外部连接的语法，请参阅参考资料。

回答by Tony Miller

The notation is still supported as of Oracle 10 (and I believe 11). It's use is considered "old fashioned", and also is not as database portable as the ANSI JOIN syntax. It's also considered much less readable, although if you come from the + background getting used to ANSI JOIN can take a little time. The important thing to know before hurling brickbats at Oracle is that they developed their + syntax before the ANSI committee had completed the definitions for the joins.

从 Oracle 10（我相信是 11）开始，该符号仍受支持。它的使用被认为是“老式的”，并且不像 ANSI JOIN 语法那样具有数据库可移植性。它也被认为可读性要低得多，尽管如果您来自 + 背景，习惯 ANSI JOIN 可能需要一些时间。在向 Oracle 大发雷霆之前，需要了解的重要一点是，他们在 ANSI 委员会完成连接定义之前开发了他们的 + 语法。

There is no performance difference; they are expressing the same thing.

没有性能差异；他们在表达同样的事情。

Edit: By "not as portable" I should have said "only supported in Oracle SQL"

编辑：通过“不那么便携”，我应该说“仅在 ​​Oracle SQL 中支持”

回答by Vincent Malgrat

I agree with Tony Miller's answer and would like to add that there are also a few things that you can NOT do with the (+) synthax:

我同意托尼米勒的回答，并想补充说，还有一些事情是你不能用 (+) 合成器做的：

• You can not FULL OUTER JOIN two tables, you have to do it manually with a UNION ALL of two joins,
• You can not OUTER JOIN a table to two or more tables, you have to manually create a subquery (ie: b.id = a.id (+) AND c.id = a.id (+)is not an acceptable clause)

• 您不能 FULL OUTER JOIN 两个表，您必须使用 UNION ALL 的两个连接手动执行此操作，
• 您不能将一个表 OUTER JOIN 到两个或多个表，您必须手动创建子查询（即：b.id = a.id (+) AND c.id = a.id (+)不是可接受的子句）

回答by schnatterer

The most comprehensive answer obviously is the one by nagul.

最全面的答案显然是nagul 的答案。

An addition for those who are looking for quick translation/mapping to the ANSI syntax:

为那些正在寻找快速翻译/映射到 ANSI 语法的人补充：

--
-- INNER JOIN
--
SELECT *
FROM EMP e
INNER JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

 -- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
     DEPT d
WHERE d.DEPTNO = e.DEPTNO;

--
-- LEFT OUTER JOIN
--
SELECT *
FROM EMP e
LEFT JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

 -- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
     DEPT d
WHERE d.DEPTNO (+) = e.DEPTNO;

--
-- RIGHT OUTER JOIN
--
SELECT *
FROM EMP e
RIGHT JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

-- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
     DEPT d
WHERE d.DEPTNO = e.DEPTNO(+);

--
-- CROSS JOIN
--
SELECT *
FROM EMP e
CROSS JOIN DEPT d;

 -- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
     DEPT d;

--
-- FULL JOIN
--
SELECT *
FROM EMP e
FULL JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

-- Synonym in deprecated oracle (+) syntax !NOT WORKING!
SELECT *
FROM EMP e,
     DEPT d
WHERE d.DEPTNO (+) = e.DEPTNO(+);

回答by Lalit Kumar B

One of the good reasons to use ANSIsyntax over the old Oracle join syntaxis that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicitjoin with older Oracle join syntax, however, with ANSI syntax you cannot miss any join as you must explicitlymention them.

使用ANSI语法而不是旧的Oracle 连接语法的一个很好的理由是，意外创建笛卡尔积的可能性为零。对于更多的表，旧的 Oracle 连接语法有可能会错过隐式连接，但是，使用 ANSI 语法，您不能错过任何连接，因为您必须明确提及它们。

Difference between Oracle outer join syntaxand the ANSI/ISO Syntax.

Oracle 外连接语法和ANSI/ISO 语法之间的区别。

LEFT OUTER JOIN -

左外连接 -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+);

SELECT e.last_name,
  d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

RIGHT OUTER JOIN -

右外连接 -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.last_name,
  d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

FULL OUTER JOIN -

全外连接 -

Before the native support of hash full outerjoin in 11gR1, Oracle would internally convert the FULL OUTER JOIN the following way -

在 11gR1 中原生支持 hash full outerjoin 之前，Oracle 会在内部按照以下方式转换 FULL OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL,
  d.department_name
FROM departments d
WHERE NOT EXISTS
  (SELECT 1 FROM employees e WHERE e.department_id = d.department_id
  );

SELECT e.last_name,
  d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

Have a look at this.

看看这个。

回答by SriniV

1. Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
2. Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
3. SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
4. Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
5. Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
6. Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
7. Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

1. 使用显式 JOIN 而不是隐式（无论它们是否是外部连接），因为使用隐式连接意外创建笛卡尔积要容易得多。使用显式 JOIN，您不能“意外”创建一个。涉及的表越多，您错过一个连接条件的风险就越高。
2. 与 ANSI 连接相比，基本上 (+) 受到严重限制。此外，它仅在 Oracle 中可用，而所有主要 DBMS 都支持 ANSI 连接语法
3. 迁移到 ANSI 语法后，SQL 不会开始表现得更好——这只是不同的语法。
4. Oracle 强烈建议您使用前面示例中显示的更灵活的 FROM 子句连接语法。过去，ANSI 语法存在一些错误，但如果您使用最新的 11.2 或 12.1，应该已经修复了。
5. 使用 JOIN 运算符可确保您的 SQL 代码符合 ANSI，从而允许前端应用程序更轻松地移植到其他数据库平台。
6. 连接条件对每个表的选择性非常低，而对理论叉积中的元组具有很高的选择性。where 语句中的条件通常具有更高的选择性。
7. Oracle 在内部将 ANSI 语法转换为 (+) 语法，您可以在执行计划的 Predicate Information 部分看到这种情况。

回答by abhishek

Oracle (+) notationis only used in Oracle, which is vendor specific. And,ANSI standared Join notationcan be used in any RDBMS (like Sql Server,MySql etc.). Otherwise,there is no difference between Oracle (+) notation and ANSI standared Join notation.

Oracle (+) 符号仅用于 Oracle，这是特定于供应商的. 并且，ANSI 标准的 Join 表示法可用于任何 RDBMS（如 Sql Server、MySql 等）。否则，Oracle (+) 表示法和 ANSI 标准 Join 表示法之间没有区别。

If you are using the ANSI standared Join notation in your Sql Query, you can use the same query in any RDBMS.And, if you are portingyour database from Oracle to any Other RDBMSin that condition you have to use ANSI Syntax.

如果您在 Sql 查询中使用 ANSI 标准连接表示法，则可以在任何 RDBMS 中使用相同的查询。而且，如果您在这种情况下将数据库从 Oracle移植到任何其他 RDBMS，则必须使用 ANSI Syntax。

回答by Ahmedov

I use (+) notation, because almost all of the Oracle Apps r12 related queries are based on that. I've not seen a single SQL query with a standard "join" expression in Oracle APPS queries(even the ones provided by Oracle itself). If you don't believe me, simply google any Oracle apps related information. For example: Fixed assets related queries

我使用 (+) 表示法，因为几乎所有与 Oracle Apps r12 相关的查询都基于此。我还没有在 Oracle APPS 查询（甚至是 Oracle 本身提供的查询）中看到带有标准“连接”表达式的单个 SQL 查询。如果您不相信我，只需在 Google 上搜索任何与 Oracle 应用程序相关的信息。例如：固定资产相关查询