What does the (+) operator in a condition mean

[mbsoul]

What does the (+) operator mean in a usage like:

WHERE A.PTID(+)=B.PTID AND B.PTID(+)=C.PTID AND C.PTID(+)=D.PTID and A.PAT_EXT_ID = '69910.0';


thanks

-mbsoul

 

[jimbopalmer]

In this case the (+) is making an outer join.

if A.PTID exists, it must equal B.PTID, but if A.PTID is NULL, that is fine. (I suspect this SHOULD be on the B side)

Let me rewrite this a little

WHERE A.PTID=B.PTID (+) AND A.PTID = C.PTID (+) AND A.PTID =D.PTID (+) and A.PAT_EXT_ID = '69910.0';

Say A.PAT_EXT_ID has to equal 69910.0
and if B.PTID exists, it must equal A.PTID, but if not make every B column a NULL
and if C.PTID exists, it must equal A.PTID, but if not make every C column a NULL
and if D.PTID exists, it must equal A.PTID, but if not make every D column a NULL

this will allow you to see every row in A that matches 69910.0, even if no row like that is in B, C, or D; but if they do match, they are shown too.

I tried to remain child-like, all I acheived was childish.

 

[grtfercho]

select emp.name,dept.Name
from employees emp, departments dept
where dept.dept_id(+)=emp.dept_id


Basically show me all the employeenames and their respective departments AND also show me the employees that don't have a department assigned.

Think of the (+) as show me all but if you don't find a match show me a null value.

This kind of syntax is being deprecated in Oracle and should be replaced with the standard syntax.

Outer Join
Inner Join
etc...

grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com