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!
This question is related to
sql
oracle
join
outer-join
The answer is
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!
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).
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 of table 'b' to table 'a'. It will return all data of table 'a' without losing its data when the other side (optional table 'b') has no data.
The modern standard syntax for the same query would be
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b ON a.id=b.id
or with a shorthand for a.id=b.id (not supported by all databases):
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b USING(id)
If you remove (+) then it will be normal inner join query
Older syntax, in both Oracle and other databases:
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id
More modern syntax:
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
INNER JOIN b ON a.id=b.id
Or simply:
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
JOIN b ON a.id=b.id
It will only return all data where both 'a' & 'b' tables 'id' value is same, means common part.
If you want to make your query a Right Join
This is just the same as a LEFT JOIN, but switches which table is optional.
Old Oracle syntax:
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id(+)=b.id
Modern standard syntax:
SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
RIGHT JOIN b ON a.id=b.id
Ref & help:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187
Source: Stackoverflow.com