Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Full outer join ?

    Hi,

    I tried the below Oracle query as an equivalent for the below SQL Server query :

    SQL Server query :

    SELECT *
    FROM fr1 FULL OUTER JOIN fr2 ON fr1.sno = fr2.frsno

    Oracle theta style : (Oracle 8i)

    select *from fr1, fr2 where fr1.sno (+) = fr2.frsno
    union
    select *from fr1, fr2 where fr1.sno = fr2.frsno (+)

    But this above Oracle query omits the duplicated rows (in the result set), but those are returned by the SQL Server query.

    Hence can anyone give the exact equivalent for SQL Server's FULL OUTER JOIN syntax in Oracle's theta style.

    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    For duplicate rows, use UNION ALL.

  3. #3
    Join Date
    Dec 2003
    Posts
    10
    use union for the sql query

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Actually, with the UNION ALL as I suggested earlier, you will get all the records that do occur in both tables, twice. So you need to exclude those from one side of the UNION ALL:

    select * from fr1, fr2 where fr1.sno (+) = fr2.frsno
    union all
    select * from fr1, fr2 where fr1.sno = fr2.frsno (+) and dr2.frsno is null

    Or equivalently:

    select * from fr1, fr2 where fr1.sno (+) = fr2.frsno
    union all
    select fr1.*, null, null, ...
    from fr1 where not exists (select null from fr2 where fr1.sno = fr2.frsno)

Posting Permissions

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