DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Full Outer Join

  1. #1
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    What is the best way of doing a full outer join between two tables ? That is, a query which gives you all the matching rows in A and B, all the rows in A but not B and all the rows in B but not A.

    Obviously you can't do:

    select a.*, b.*
    from a, b
    where a.key (+) = b.key (+)

    Is the only option to do it as two separate queries i.e. something like

    select a.*, b.*
    from a, b
    where a.key = b.key(+)
    union
    select a.*, b.*
    from a, b
    where a.key(+) = b.key

    Or is there a neater way ?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Outer join is not supported by all RDBMS. Of course, Oracle supports one way outer join.
    The other way of working is using NOT EXISTS.
    Ex for one way outer join:
    SELECT A.COL1, B.COL1
    FROM A ,B
    WHERE A.COL1 = B.COL1
    UNION
    SELECT A.COL1, ' '
    FROM A
    WHERE NOT EXISTS
    (SELECT B.COL1 FROM B
    WHERE A.COL1 = B.COL1);

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    ...or wait for 9i

    - Chris

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