Oracle "(+)" Operator
join, oracle, outer-join, sql
Solution
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` 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:
Problem
I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them. The DBMS is Oracle. I did not understand a statement which read like this: ``` select ... from a,b where a.id=b.id(+) ``` I am confused about the `(+)` operator, and could not get it at any forums... (searching for + within quotes didn't work either). Anyway, I used 'Explain Plan' of SQLDeveloper and I got an output saying that `HASH JOIN, RIGHT OUTER`, etc. Would there be any difference if I remove the `(+)` operator at the end of the query? Does the database have to satisfy some condition (like having some indexes, etc.) before `(+)` can be used?