Thread: Outer join differences
Hi, I see different results in Oracle and postgres for same outer join queries. Here are the details. I have the following tables in our pg db table: yuva_test1 yt1_id yt1_name yt1_descr 1 1-name1 1-desc1 2 1-name2 1-desc2 3 1-name3 1-desc3 4 1-name4 1-desc4 5 1-name5 1-desc5 6 1-name6 1-desc6 table: yuva_test2 yt2_id yt2_name yt2_descr 2 2-name2 2-desc2 3 2-name3 2-desc3 4 2-name4 2-desc4 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 Why postgres is giving? which is standard? is it a bug? or is it the way postgres is implemented? Could some one help me? Note: at the end of my mail is script to create tables and data in postgres. Thanks Yuva Sr. Java Developer www.ebates.com ============================================================ Scripts: CREATE TABLE "yuva_test1" ( "yt1_id" numeric(16, 0), "yt1_name" varchar(16) NOT NULL, "yt1_descr" varchar(32) ) WITH OIDS; CREATE TABLE "yuva_test2" ( "yt2_id" numeric(16, 0), "yt2_name" varchar(16) NOT NULL, "yt2_descr" varchar(32) ) WITH OIDS; insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (1, '1-name1', '1-descr1'); insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (2, '1-name2', '1-descr2'); insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (3, '1-name3', '1-descr3'); insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (4, '1-name4', '1-descr4'); insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (5, '1-name5', '1-descr5'); insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (6, '1-name6', '1-descr6'); insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (2, '2-name2', '2-descr2'); insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (3, '2-name3', '2-descr3'); insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (4, '2-name4', '2-descr4'); ============================================================
Yuva Chandolu <ychandolu@ebates.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
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@sss.pgh.pa.us]
Sent: Tuesday, July 30, 2002 9:15 PM
To: Yuva Chandolu
Cc: 'pgsql-hackers@postgresql.org'
Subject: Re: [HACKERS] Outer join differences
Yuva Chandolu <ychandolu@ebates.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
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.
> > 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 Probaly if you change your postgres query to this, it will give the same answer as Oracle: 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'; ?? Chris > > 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 And maybe if you change the oracle query to this, it will give the same answer as postgres: select yt1_name, yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where yt1_id=yt2_id(+) and yt2_name = '2-name2'(+); Just guessing tho. Chris
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:sszabo@megazone23.bigpanda.com] Sent: Tuesday, July 30, 2002 9:31 PM To: Yuva Chandolu Cc: 'pgsql-hackers@postgresql.org' 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.
> This is great, we thought we may go for code changes, we will go with this
> solution instead.
But you did catch Stephan's point that an outer join is not required to
produce the result you apparently want? The equivalent inner join will
be at worst just as fast, and possibly faster, both for PostgreSQL and
for Oracle...
- Thomas
> > Here are the details. > > Those probably aren't the same outer join queries. I think you're right, these aren't the same, see below: > > > 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 I think for Oracle the equivalent is: select yt1_name, yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where yt2_id (+)= yt1_id=yt2_id and yt2_name (+)= '2-name2'
Yuva, The results make sense to me. The left outer join functionality in Postgres is explained as follows: LEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e., all combined rows that pass its ON condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the ON condition. This left-hand row is extended to the full width of the joined table by inserting NULLs for the right-hand columns. Note that only the JOIN's own ON or USING condition is considered while deciding which rows have matches. Outer ON or WHERE conditions are applied afterwards. So, in your postgres statement, you are retrieving all rows from yuva_test1, and the one row from yuva_test2 that satisfied the "where" criteria that yt2_name = '2-name2'. In Oracle, though, since your outer join is on yuva_test2, you would need to specify an outer join on the criterion "yt2_name = '2-name2''" by saying "yt2_name (+) = '2-name2''" to limit the resultset. Hope this helps Jill > -----Original Message----- > From: Yuva Chandolu > Sent: Tuesday, July 30, 2002 8:53 PM > To: 'pgsql-hackers@postgresql.org' > Subject: Outer join differences > > Hi, > > I see different results in Oracle and postgres for same outer join > queries. Here are the details. > > I have the following tables in our pg db > > table: yuva_test1 > yt1_id yt1_name yt1_descr > 1 1-name1 1-desc1 > 2 1-name2 1-desc2 > 3 1-name3 1-desc3 > 4 1-name4 1-desc4 > 5 1-name5 1-desc5 > 6 1-name6 1-desc6 > > table: yuva_test2 > yt2_id yt2_name yt2_descr > 2 2-name2 2-desc2 > 3 2-name3 2-desc3 > 4 2-name4 2-desc4 > > 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 > > Why postgres is giving? which is standard? is it a bug? or is it the way > postgres is implemented? Could some one help me? > > Note: at the end of my mail is script to create tables and data in > postgres. > > Thanks > Yuva > Sr. Java Developer > www.ebates.com > > ============================================================ > Scripts: > CREATE TABLE "yuva_test1" ( > "yt1_id" numeric(16, 0), > "yt1_name" varchar(16) NOT NULL, > "yt1_descr" varchar(32) > ) WITH OIDS; > > CREATE TABLE "yuva_test2" ( > "yt2_id" numeric(16, 0), > "yt2_name" varchar(16) NOT NULL, > "yt2_descr" varchar(32) > ) WITH OIDS; > > insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (1, '1-name1', > '1-descr1'); > insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (2, '1-name2', > '1-descr2'); > insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (3, '1-name3', > '1-descr3'); > insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (4, '1-name4', > '1-descr4'); > insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (5, '1-name5', > '1-descr5'); > insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (6, '1-name6', > '1-descr6'); > > insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (2, '2-name2', > '2-descr2'); > insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (3, '2-name3', > '2-descr3'); > insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (4, '2-name4', > '2-descr4'); > ============================================================