Hi Tom, Thanks for your prompt reply, after second thought(before receiving your reply) I realized that postgres is doing more logically - i.e if the outer join condition returns false then replace by nulls for right table columns. We may change our code accordingly :-(.
Thanks Yuva -----Original Message----- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 9:15 PM To: Yuva Chandolu Cc: '[EMAIL PROTECTED]' Subject: Re: [HACKERS] Outer join differences Yuva Chandolu <[EMAIL PROTECTED]> writes: > I see different results in Oracle and postgres for same outer join queries. I believe you are sending your bug report to the wrong database. > 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 > yt1_name yt1_descr yt2_name yt2_descr > 1-name1 1-descr1 > 1-name2 1-descr2 2-name2 2-descr2 > 1-name3 1-descr3 > 1-name4 1-descr4 > 1-name5 1-descr5 > 1-name6 1-descr6 > 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 results > yt1_name yt1_descr yt2_name yt2_descr > 1-name2 1-descr2 2-name2 2-descr2 According to the SQL spec, the output of a LEFT JOIN consists of those joined rows where the join condition is true, plus those rows of the left table for which no right-table row produced a true join condition (substituting nulls for the right-table columns). Our output clearly conforms to the spec. I do not know what Oracle thinks is the correct output when one condition is marked with (+) and the other is not --- it's not very obvious what that corresponds to in the spec's terminology. But I suggest you take it up with them, not us. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
