Selection predicate on an outer joined table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Selection predicate on an outer joined table

  1. #1
    Join Date
    Sep 2005
    Posts
    46

    Question 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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by issacgeorge
    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?
    Code:
    select empno, ename, deptname
    from emp, dept
    where emp.deptno = dept.deptno (+) and 
              dept.LOCATION(+) = 'TVM'
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    or, how about this one?

    Code:
    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;
    this space intentionally left blank

  4. #4
    Join Date
    Oct 2005
    Posts
    3
    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.

    Code:
    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
    Code:
    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
    Code:
    from emp, dept
    where emp.deptno(+) = dept.deptno
    or
    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width