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'); ============================================================ ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
