Skip site navigation (1) Skip section navigation (2)

Re: Outer join differences

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Yuva Chandolu <ychandolu(at)ebates(dot)com>
Cc: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Outer join differences
Date: 2002-07-31 04:14:32
Message-ID: 24562.1028088872@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2002-07-31 04:16:49
Subject: Re: Rules and Views
Previous:From: Hannu KrosingDate: 2002-07-31 04:06:23
Subject: Re: Why is MySQL more chosen over PostgreSQL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group