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

2021-12-01 00:00:00 join performance sql oracle

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

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

性能上有区别吗?

不推荐使用加号符号吗?

Is the plus notation deprecated?

推荐答案

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 连接语法没有的限制.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 连接运算符.使用 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 子句中，或者出现在左相关的上下文中(当指定 TABLE子句)在 FROM 子句中，并且只能应用于表或视图的列.
  • 如果 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;

  • (+) 运算符只能应用于列，不能应用于任意表达式.但是，任意表达式可以包含一个或多个用 (+) 运算符标记的列.
  • 包含 (+) 运算符的 WHERE 条件不能与使用 OR 逻辑运算符的另一个条件组合.
  • 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.

      在执行两对以上表的外连接的查询中，单个表可以是仅另一个表的空生成表.因此，您不能将(+) 运算符应用于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.

相关文章