Re: Outer join differences

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(at)megazone23(dot)bigpanda(dot)com]
Sent: Tuesday, July 30, 2002 9:31 PM
To: Yuva Chandolu
Cc: 'pgsql-hackers(at)postgresql(dot)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.