Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    18

    Question Unanswered: Converting a (+) to an outer join in SQLServer

    Hi,
    I'm having difficulties in translating the Oracle syntax into SQLServer.
    Actually, I'm trying to translate something like :

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1, Tab2
    Where Tab1.a = Tab2.c (+)

    I tried :

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1
    RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

    The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

    Can anybody help ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Converting a (+) to an outer join in SQLServer

    Originally posted by Linirlan
    Hi,
    I'm having difficulties in translating the Oracle syntax into SQLServer.
    Actually, I'm trying to translate something like :

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1, Tab2
    Where Tab1.a = Tab2.c (+)

    I tried :

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1
    RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

    The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

    Can anybody help ?
    Counterintuitively, the ANSI equivalent here would be LEFT OUTER JOIN not RIGHT OUTER JOIN.

  3. #3
    Join Date
    Dec 2002
    Posts
    104

    Re: Converting a (+) to an outer join in SQLServer

    Originally posted by Linirlan
    Hi,
    I'm having difficulties in translating the Oracle syntax into SQLServer.
    Actually, I'm trying to translate something like :

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1, Tab2
    Where Tab1.a = Tab2.c (+)

    I tried :

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1
    RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

    The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

    Can anybody help ?
    hello ,

    Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
    from Tab1,Tab2
    where Tab1.a =* Tab2.c

    here it will includes all rows for Tab2 and Tab1 is a null suppling table.

    hope this will help,
    Pooja
    Last edited by pooja; 07-18-03 at 07:20.

  4. #4
    Join Date
    Jul 2003
    Posts
    18
    Thanks for your answers but err...
    I'm sorry but (I'm tired this morning) there's a mistake in my question...

    My problem occurs when compared to a constant :

    SELECT
    TAB1.A,
    TAB1.B
    FROM
    TAB2,
    TAB1
    WHERE
    TAB1.A= TAB2.C (+) AND
    TAB2.D(+) = -1

    How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

    Thx

  5. #5
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by Linirlan
    Thanks for your answers but err...
    I'm sorry but (I'm tired this morning) there's a mistake in my question...

    My problem occurs when compared to a constant :

    SELECT
    TAB1.A,
    TAB1.B
    FROM
    TAB2,
    TAB1
    WHERE
    TAB1.A= TAB2.C (+) AND
    TAB2.D(+) = -1

    How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

    hello,

    i understand from this is that Tab2 is an outer table and u want to restirct the records on outer table...if my understanding is right

    try removing (+) from TAB2.d (+)= -1 condition

    guess so,
    Pooja

    Thx

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Linirlan
    Thanks for your answers but err...
    I'm sorry but (I'm tired this morning) there's a mistake in my question...

    My problem occurs when compared to a constant :

    SELECT
    TAB1.A,
    TAB1.B
    FROM
    TAB2,
    TAB1
    WHERE
    TAB1.A= TAB2.C (+) AND
    TAB2.D(+) = -1

    How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

    Thx
    What is does is only try to match TAB1 records to TAB2 records where D = -1.

    I think with ANSI syntax you probably have to do the filter before the outer join:

    SELECT TAB1.A, TAB1.B
    FROM TAB1
    LEFT OUTER JOIN (SELECT * FROM TAB2 WHERE d=-1) t2
    ON TAB1.a = T2.c;

    But unless you actually want to select some columns from TAB2, this outer join is pointless - i.e. the result will be same as:

    SELECT TAB1.A, TAB1.B
    FROM TAB1;

  7. #7
    Join Date
    Jul 2003
    Posts
    18
    This is very useful, thank you for your help.

    (and yes, I'll select some columns from TAB2)

  8. #8
    Join Date
    Jul 2003
    Posts
    18
    I found another way to do it, maybe it is more quick to execute...

    SELECT TAB1.A, TAB1.B, TAB2.e
    FROM TAB1
    LEFT OUTER JOIN TAB2 ON TAB1.a = TAB2.c AND TAB2.d=-1;

    What do you think ?

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Linirlan
    I found another way to do it, maybe it is more quick to execute...

    SELECT TAB1.A, TAB1.B, TAB2.e
    FROM TAB1
    LEFT OUTER JOIN TAB2 ON TAB1.a = TAB2.c AND TAB2.d=-1;

    What do you think ?
    Yes, if that works it's probably better than using an inline view. I can't check at the moment (no 9i here), but I had a doubt about doing that for some reason I can't remember.

  10. #10
    Join Date
    Jul 2012
    Posts
    3

    Thumbs up

    Quote Originally Posted by andrewst View Post
    What is does is only try to match TAB1 records to TAB2 records where D = -1.

    I think with ANSI syntax you probably have to do the filter before the outer join:

    SELECT TAB1.A, TAB1.B
    FROM TAB1
    LEFT OUTER JOIN (SELECT * FROM TAB2 WHERE d=-1) t2
    ON TAB1.a = T2.c;

    But unless you actually want to select some columns from TAB2, this outer join is pointless - i.e. the result will be same as:

    SELECT TAB1.A, TAB1.B
    FROM TAB1;
    Wonderful Solution!! Saved my Day !!

Posting Permissions

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