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 ?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.|||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|||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|||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|||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;|||This is very useful, thank you for your help.

(and yes, I'll select some columns from TAB2)|||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 ?|||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.