From: | Yuva Chandolu <ychandolu(at)ebates(dot)com> |
---|---|
To: | 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Outer join differences |
Date: | 2002-07-31 04:29:48 |
Message-ID: | A0F24737FCB34F489EC955D143BDD8510173E0F1@exchange-sf1.corp.ebates.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, July 30, 2002 9:15 PM
To: Yuva Chandolu
Cc: 'pgsql-hackers(at)postgresql(dot)org'
Subject: Re: [HACKERS] Outer join differences
Yuva Chandolu <ychandolu(at)ebates(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Curt Sampson | 2002-07-31 04:31:15 | Re: Why is MySQL more chosen over PostgreSQL? |
Previous Message | Tom Lane | 2002-07-31 04:29:18 | Re: Open 7.3 items |