This is great, we thought we may go for code changes, we will go with this solution instead.
Thanks Yuva -----Original Message----- From: Stephan Szabo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 9:31 PM To: Yuva Chandolu Cc: '[EMAIL PROTECTED]' Subject: Re: [HACKERS] Outer join differences On Tue, 30 Jul 2002, Yuva Chandolu wrote: > Hi, > > I see different results in Oracle and postgres for same outer join queries. > Here are the details. Those probably aren't the same outer join queries. > When I run the query "select yt1_name, yt1_descr, yt2_name, yt2_descr from > yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id and yt2_name = > '2-name2'" on postgres database I get the following results > Both conditions are part of the join condition for the outer join. > But when I tried the same on Oracle(8.1.7) (the query is "select yt1_name, > yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where > yt1_id=yt2_id(+) and yt2_name = '2-name2'') I get the following result One condition is the join condition and one is a general where condition I would guess since only one has the (+) I think the equivalent query is select yt1_name, yt1_descr, yt2_name, yt2_descr from yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id where yt2_name='2-name2'. Note of course that you're destroying the outer joinness by doing that yt2_name='2-name2' since the rows with no matching yuva_test2 will not match that conditoin. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]