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.

Tue, 30 Jul 2002 21:46:57 -0700
Yuva Chandolu <ychandolu@ebates.com>
Re: Outer join differences
> 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

Tue, 30 Jul 2002 23:18:41 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: Outer join differences
> > 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'


Wed, 31 Jul 2002 09:28:02 +0200
Mario Weilguni <mweilguni@sime.com>
Re: Outer join differences