Selection predicate on an outer joined table

[issacgeorge]

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

[jmodic]

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..

The outer join syntax you are using is also Oracle specific, so wher's the point in not using NVL()?

Anyway, how about this one?

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

[gandolf989]

or, how about this one?

select emp.empno, emp.ename, dept.deptname
  from emp
  left outer join
     ( select deptno, deptname
         from 
        where LOCATION = 'TVM' ) dept
    on emp.deptno = dept.deptno;

[aslan23]

First, your outer join is being "nullified" because your additional selection criteria is more selective than your query. However, even without the additional criteria, you are trying to query all departments whether or not they have any employees. If your example is based on modifications to the Scott schema, there are no employees without departments unless you have added some. In this case the result of an outer join is the same as an inner join.

from emp, dept
where emp.deptno = dept.deptno(+)

The plus in parenthesis (+) means add a blank if there aren't any. The way you've coded this (above) means that any employee who is not in a department will be displayed with NULL department information. Because you have attached to it the additional criteria that that you only want to see rows where the department location is 'TVM' you will not see any employees that have no department... because their department is, by definition, NULL. You have effectively nullified your outer join by additional selection criteria even if you have employees who are in no department.

The equivalent join clause for your code above is

from emp left outer join dept
on emp.deptno = dept.deptno

The keyword "left" indicates that you want to see all rows from the table on the left of the word join... this is the opposite of the placement of (+).

As I've said, in the Scott schema there are no employees without departments. But there are departments with no employees. If your tables have departments with no employees and you want to see all departments with or without employees you would query

from emp, dept
where emp.deptno(+) = dept.deptno

or

from emp right outer join dept
on emp.deptno = dept.deptno

But be careful of selection criteria that is more restrictive than your outer join.