Selection predicate on an outer joined table
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