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 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!

Answer 1

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:

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!

Answer 3

In Oracle, (+) denotes the "optional" table in the JOIN. So in your query,

select  a.id, b.id, a.col_2, b.col_2, ...
from a,b
where a.id=b.id(+)

it's a LEFT OUTER JOIN 'b' table with 'a' table. As like modern left join query. ( it will returns all data of 'a' table without loosing its data on the other side optional table 'b' can lost his data )

select  a.id, b.id, a.col_2, b.col_2, ...
from a
Left join b ON a.id=b.id

OR

select  a.id, b.id, a.col_2, b.col_2, ...
from a
Left join b using(id)

now if you remove (+) then it will be normal inner join query,

select  a.id, b.id, a.col_2, b.col_2, ...
from a,b
where a.id=b.id

it will only returns all data where both 'a' & 'b' tables 'id' value is same, means common part.

Extra: If you want to make your query as Right join in older format or modern then it will appear as like bellow:

Old:

select  a.id, b.id, a.col_2, b.col_2, ...
from a,b
where a.id(+)=b.id

Modern:

select  a.id, b.id, a.col_2, b.col_2, ...
from a
Right join b ON a.id=b.id

OR

select  a.id, b.id, a.col_2, b.col_2, ...
from a
Right join b using(id)

Ref & help:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187

Left Outer Join using + sign in Oracle 11g

https://www.w3schools.com/sql/sql_join_left.asp

Answer 4

In practice, the + symbol is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or null values within the conditional).