SQL 9i OUTER JOIN QUERY

[Rahul Khandpur]

hi friends,
i have one query related to oracle9i outer join condition

it is mentioned that we can't use (+) with the operand of OR
and IN operators

example

select e.last_name,e.department_id,d.department_name,d.de partment_id
from employees e , departments d
where e.department_id(+) = d.department_id
or d.department_id = 100

this gives error =>

where e.department_id(+) = d.department_id
*
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
*************************************************
but this query works fine with the IN operator, i couldn't find the
reson how the following query works perfectly with IN and (+)
operator.
select e.last_name,e.department_id,d.department_name,d.de partment_id
from employees e , departments d
where e.department_id(+) in ( d.department_id)
so friends please help to find reason for this.

[Brian Marasca]

Hi, Rahul. What are you trying to do? Saying 'WHERE E.DEPARTMENT_ID IN
(D.DEPARTMENT_ID)' doesn't even look like legal SQL to me. And I can't
imagine what '(+)' could mean in that construct. What happened to the
DEPARTMENT_ID = 100 in the example using IN? Since you are joining over
DEPARTMENT_ID, you are going to get all of them anyway.

As a beginning, you should restructure your syntax to eliminate the archaic
outer join syntax in favor of the proper syntax. Joins do not belong in the
WHERE clause. There is never a time when the long-deprecated '(+)' operator
should be used.

SELECT
E.LAST_NAME,
D.DEPARMENT_NAME,
D.DEPARTMENT_ID

FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

Apologies, but I can't understand how the 'DEPARTMENT_ID = 100' fits into
this. The above query will already return department 100 data as long as
there is any employee in that department. If you want the department 100
data even in the case that no one is in that department, you should add a
UNION to that effect:

SELECT
E.LAST_NAME,
D.DEPARMENT_NAME,
D.DEPARTMENT_ID

FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

UNION

SELECT
E.LAST_NAME,
D.DEPARMENT_NAME,
D.DEPARTMENT_ID

FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

WHERE D.DEPARTMENT_ID = 100;

If I haven't understood you and you still need more help, could you just
state in English what you'd like to get back from the server, rather than
posting your code, and I'll try to give you a query to get what you're
looking for?

I hope this was helpful, but I'm not certain enough that I understand what
you're trying to do.

Brian