What's the difference between using oracle's plus notation (+)
over the ansi standard join
notation?
Is there a difference in performance?
Is the plus notation deprecated?
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.
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 recommends that you use the
FROM
clauseOUTER 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 theFROM
clauseOUTER JOIN
syntax:
- You cannot specify the
(+)
operator in a query block that also containsFROM
clause join syntax.- The
(+)
operator can appear only in theWHERE
clause or, in the context of left-correlation (when specifying theTABLE
clause) in theFROM
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.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
WHERE
condition containing the(+)
operator cannot be combined with another condition using theOR
logical operator.- A
WHERE
condition cannot use theIN
comparison condition to compare a column marked with the(+)
operator with an expression.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.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 toSELECT
for the syntax for an outer join.