right outer joins
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: right outer joins

  1. #1
    Join Date
    Jul 2005
    Posts
    23

    Question right outer joins

    I have a simple query that uses a right outer join, ive used the RIGHT OUTER JOIN method and also =(+) method. Both queries should be identical, but they return different numbers of rows. Am I missing something really obvious here

    query 1

    select bc.id,bct.id AS "role_id",
    bct.description AS "role_name",
    bc.contributor_name AS "contributor"
    FROM billings_contributions bc RIGHT OUTER JOIN billings_contrib_types bct
    ON bct.id = bc.contribution_type_id
    WHERE bct.common_flag = 'Y'
    ORDER BY bct.app_seq desc



    query 2

    select bc.id,
    bct.id AS "role_id",
    bct.description AS "role_name",
    bc.contributor_name AS "contributor"
    from billings_contributions bc,
    billings_contrib_types bct

    where bct.id = bc.contribution_type_id(+)
    and bct.common_flag = 'Y'
    order by bct.app_seq desc

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    In your ANSI syntax for outer join you shoudnt be using the where clause. The queries will show you the difference.
    The first one will not return the outer join results and second one
    will return what you expect.


    PHP Code:
    SELECT *
    FROM   EMP e RIGHT OUTER JOIN DEPT d 
      ON   e
    .deptno d.deptno
    WHERE  e
    .sal 1600


    SELECT 
    *
    FROM   EMP e RIGHT OUTER JOIN DEPT d 
      ON   e
    .deptno d.deptno AND e.sal 1600 
    HTH
    -dharma

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Sorry misuk11, I can't see anything.


    dharma, you're right about the placing of conditions, but in this case a better parallel is:
    Code:
    SELECT *
    FROM   EMP e RIGHT OUTER JOIN DEPT d
      ON   e.deptno = d.deptno
    WHERE  d.dname IN ('OPERATIONS','RESEARCH')
    
    gives the same as:
    
    SELECT *
    FROM   EMP e, DEPT d
    WHERE  e.deptno(+) = d.deptno
    AND    d.dname IN ('OPERATIONS','RESEARCH')
    
    but not:
    
    SELECT *
    FROM   EMP e RIGHT OUTER JOIN DEPT d
      ON   e.deptno = d.deptno AND d.dname IN ('OPERATIONS','RESEARCH')
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Dec 2000
    Posts
    138
    Yep, didnt notice that.
    Thanks

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