Oracle的加(+)表示法和ansi JOIN表示法之间的区别? [英] Difference between Oracle's plus (+) notation and ansi JOIN notation?

问题描述

在ANSI标准join表示法上使用oracle的加号(+)和有什么区别?

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

性能上有区别吗?

不赞成使用加号吗?

推荐答案

AFAIK，(+)表示法仅用于向后兼容，因为Oracle在将ANSI标准用于联接之前将其首次使用.它特定于Oracle，并且在存在等效的符合标准的版本时，您应该避免在新代码中使用它.

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.

似乎两者之间存在差异，并且(+)表示法具有ANSI join语法所没有的限制. Oracle自己建议您不要使用(+)表示法. Oracle®数据库SQL语言参考中的完整描述 11g第1版(11.1):

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):

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:

• 您不能在还包含FROM子句连接语法的查询块中指定(+)运算符.
• (+)运算符只能出现在WHERE子句中，或者在FROM子句的左相关(指定TABLE子句时)的上下文中出现，并且只能应用于列表或视图.
• 如果A和B通过多个联接条件联接，则必须在所有这些条件下使用(+)运算符.如果不这样做，那么Oracle数据库将仅返回由简单联接产生的行，而不会发出警告或错误来通知您没有外部联接的结果.
• 如果在外部查询中指定一个表，而在内部查询中指定另一个表，则(+)运算符不会产生外部联接.
• 尽管自联接有效，但是您不能使用(+)运算符将表外部联接到自身.

• You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
• The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, 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.

例如，以下语句无效:

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;

• (+)运算符只能应用于列，不能应用于任意表达式.但是，任意表达式可以包含一个或多个用(+)运算符标记的列.
• 使用OR逻辑运算符不能将包含(+)运算符的WHERE条件与另一个条件组合.
• WHERE条件不能使用IN比较条件将用(+)运算符标记的列与表达式进行比较.
• 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 WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
• A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.

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

If the WHERE clause 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.

在执行多于两对表的外部联接的查询中，单个表可以是仅一个其他表的由null生成的表.因此，对于A和B的连接条件以及B和C的连接条件，不能将(+)运算符应用于B的列.有关外部连接的语法，请参考SELECT.

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 SELECT for the syntax for an outer join.

这篇关于Oracle的加(+)表示法和ansi JOIN表示法之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！