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?? It would be greatly helpful if you can provide me a simple understanding, or some good links where I can read about this.
Thanks!
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: