Re: Outer join differences

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(at)postgresql(dot)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');
> ============================================================