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

    Unanswered: Outer join queries with OR operators

    Hi,

    SQL Server Query :

    select *from test left outer join test1 on test.sno = test1.sno where test.sno is null or test1.sno is null

    I tried converting the above to an Oracle query as follows :

    SELECT * FROM test, test1 WHERE test.sno = test1.sno (+) AND test.sno is null OR test1.sno is null

    But in Oracle, "OR" operators can't be used with the Outer join.

    Please advice,

    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Presumably you are on 8i, otherwise you could use the ANSI join syntax in Oracle too.

    In this case, I don't think you need the OR, because:

    test.sno is null => test1.sno is null

    i.e. if test.sno is null, then the join will fail, and so test1.sno will be null.

    So the query simplifies to:

    SELECT * FROM test, test1 WHERE test.sno = test1.sno (+) AND test1.sno is null;

    However, if that had not been the case and you really needed the OR, then you would have to replace it by a UNION of the 2 cases:

    SELECT * FROM test, test1 WHERE test.sno = test1.sno (+) AND test.sno is null
    UNION
    SELECT * FROM test, test1 WHERE test.sno = test1.sno (+) AND test1.sno is null

Posting Permissions

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