From: | Yuva Chandolu <ychandolu(at)ebates(dot)com> |
---|---|
To: | "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Outer join differences |
Date: | 2002-07-31 03:53:06 |
Message-ID: | A0F24737FCB34F489EC955D143BDD8510173E0F0@exchange-sf1.corp.ebates.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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');
============================================================
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-07-31 04:00:29 | Re: Open 7.3 items |
Previous Message | Tom Lane | 2002-07-31 03:51:38 | Re: WAL file location |