Hai every body,

I have two tables, say A and B, which are outer joined together. So i'll get rows from A which do not have matching rows in B. But this is not working when I give a selection filter on table B. That is the outer join effect is getting nullified. Can any body suggest a solution...

For ex:

select empno, ename, deptname
from emp, dept
where emp.deptno = dept.deptno (+) and
dept.LOCATION = 'TVM'

Here since I have given a selection filter on dept, rows from emp which donot have a matching row on dept won't be retrieved.

One obvious solution is to use an inline view as given below:

select empno, ename, deptname
from emp, (select * from dept where dept.LOCATION = 'TVM') dept
where emp.deptno = dept.deptno (+)

But it is an additional overhead...

Again,
select empno, ename, deptname
from emp, dept
where emp.deptno = dept.deptno (+) and
NVL(dept.LOCATION, 'TVM') = 'TVM'

But NVL is oracle specific.. so that too is not recommended..

Can any body suggest a better alternative....

Regards
Issac