Difference between Oracle's plus (+) notation and ansi JOIN notation?
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.
Oracle Joins - Comparison between conventional syntax VS ANSI Syntax
Grouping answers together
- 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.
- 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
- SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
- 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.
- 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.
- 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.
- Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.
Possible Pitfall in using ANSI syntax on 12c engine
Including a possibility of bug in JOIN in 12c. See here
FOLLOW UP:
Quest SQL optimizer tool
rewrites the SQL to ANSI syntax.
Is there an Oracle official recommendation on the use of explicit ANSI JOINs vs implicit joins?
I haven't seen it if there is. The reason for preferring ANSI syntax for outer joins in particular (apart from the non-standarrd, Oracle-specific (+)
symbol) is that more outer joins are expressible using the ANSI syntax. The restriction "ORA-01417: a table may be outer joined to at most one other table" applies to (+)
outer joins but not to ANSI outer joins. Other restrictions on (+)
that do not apply to ANSI outer joins are documented here.
One highly respected Oracle expert actually recommends sticking to the old syntax for inner joins - see Jonathan Lewis's blog. He says there that ANSI joins are transformed to traditional Oracle joins under the covers anyway. I don't agree with him 100% (I prefer ANSI joins myself in general), but would not claim to have a fraction of his knowledge on the topic.
In a nutshell, ANSI outer joins are technically superior to old (+)
joins, whereas with inner joins it is more just a matter of style.
What is the purpose of (+) operator in a where clause, other than outer joins, in Oracle SQL?
It's the same as:
select *
from table_a a
left outer join table_b b
on a.id = b.id
and b.type_cd = 'DOLLR'
and b.seq_nb = 1
Sometimes also referred to as a "filtered outer join".
It is equivalent to an outer join with a derived table:
select *
from table_a a
left outer join (
select *
from table_b
where b.type_cd = 'DOLLR'
and b.seq_nb = 1
) b on a.id = b.id
Oracle (+) Operator
That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.
The query would be re-written in ANSI-92 syntax as:
SELECT ...
FROM a
LEFT JOIN b ON b.id = a.id
This link is pretty good at explaining the difference between JOINs.
It should also be noted that even though the (+)
works, Oracle recommends not using it:
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:
NUMBER functions (+)
(+)
is the (old) outer join operator in Oracle. It is specifying an outer join between the receipt_num
columns of the receipt_note
table and the receipt_data
table.
This syntax is obsolete; new queries should use OUTER JOIN
instead since it is more readable.
what is (+) syntax in SQL query
It is the obsolete outer join symbol.
In Oracle, (+) denotes the "optional" table in the JOIN.
You may check out this for Left and Right Outer Joins.
On a side note:-(Although its obsolete)
The placement of the (+) determines RIGHT or LEFT. If the (+) is on the right, it's a LEFT JOIN and if (+) is on the left, it's a RIGHT JOIN.
What does the symbol (+) mean at the end of a WHERE statement?
It could be use to do joins, but its not recommended:
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.
Edit: 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.
From here: Difference between Oracle's plus (+) notation and ansi JOIN notation?
In Oracle, (+) denotes the "optional" table in the JOIN.
From here: Left Outer Join using + sign in Oracle 11g
Related Topics
Cannot Insert Explicit Value For Identity Column in Table 'Table' When Identity_Insert Is Set to Off
How to Get Column Names from a Table in SQL Server
Unknown Column in Where Clause
Why Would an in Condition Be Slower Than "=" in Sql
Why Isn't SQL Ansi-92 Standard Better Adopted Over Ansi-89
Combining "Like" and "In" For SQL Server
How to Use an Insert Statement'S Output Clause to Get the Identity Value
How to Pass Parameters to Query
Simplest Way to Do a Recursive Self-Join
Maximum Size For a SQL Server Query? in Clause? Is There a Better Approach
MySQL - Error 1045 - Access Denied
SQL to Linq With Multiple Join, Count and Left Join