Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

What does the (+) operator in a condition mean

Status
Not open for further replies.

mbsoul

Technical User
Jul 20, 2005
1
US
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
 
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.
 
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
-----------------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top