Oracle "(+)" Operator

Question

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 too )

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 (+) operator at the end of the query? Does the database has 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!

Answer 1

That's Oracle specific notation for a LEFT 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.

Answer 2

The (+) operator indicates an outer join. This means that Oracle will still return records from the other side of the join even when there is no match. For example if a and b are emp and dept and you can have employees unassigned to a department then the following statement will return details of all employees whether or not they've been assigned to a department.

select * from emp, dept where emp.dept_id=dept.dept_id(+)

So in short, removing the (+) may make a significance difference but you might not notice for a while depending on your data!