Outer join differences

Yuva Chandolu <ychandolu@ebates.com> writes:

I see different results in Oracle and postgres for same outer join queries.

I believe you are sending your bug report to the wrong database.

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

According to the SQL spec, the output of a LEFT JOIN consists of those
joined rows where the join condition is true, plus those rows of the
left table for which no right-table row produced a true join condition
(substituting nulls for the right-table columns). Our output clearly
conforms to the spec.

I do not know what Oracle thinks is the correct output when one
condition is marked with (+) and the other is not --- it's not very
obvious what that corresponds to in the spec's terminology. But I
suggest you take it up with them, not us.

regards, tom lane

Hi Tom,

Thanks for your prompt reply, after second thought(before receiving your
reply) I realized that postgres is doing more logically - i.e if the outer
join condition returns false then replace by nulls for right table columns.
We may change our code accordingly :-(.

Thanks
Yuva

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, July 30, 2002 9:15 PM
To: Yuva Chandolu
Cc: 'pgsql-hackers@postgresql.org'
Subject: Re: [HACKERS] Outer join differences

Yuva Chandolu <ychandolu@ebates.com> writes:

I see different results in Oracle and postgres for same outer join

queries.

I believe you are sending your bug report to the wrong database.

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

According to the SQL spec, the output of a LEFT JOIN consists of those
joined rows where the join condition is true, plus those rows of the
left table for which no right-table row produced a true join condition
(substituting nulls for the right-table columns). Our output clearly
conforms to the spec.

I do not know what Oracle thinks is the correct output when one
condition is marked with (+) and the other is not --- it's not very
obvious what that corresponds to in the spec's terminology. But I
suggest you take it up with them, not us.

regards, tom lane

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.

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

Probaly if you change your postgres query to this, it will give the same
answer as Oracle:

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';

??

Chris

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

And maybe if you change the oracle query to this, it will give the same
answer as postgres:

select yt1_name,
yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where
yt1_id=yt2_id(+) and yt2_name = '2-name2'(+);

Just guessing tho.

Chris

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.

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

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'

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