Re: Left join bug?

"joe" <fischauto333_at_yahoo.de> wrote in message news:1186139409.491400.128680_at_d55g2000hsg.googlegroups.com...
> Hi,
>
> create table a (num integer)
> create table b (num integer)
>
> insert into a values (1)
> insert into b values (2)
>
> select * from a left join b
> on b.num = 123
> where a.num = 1 and b.num is null
>
>
> MySQL & Oracle 10i show the following result:
> 1 null
>
> Oracle 9.2.0.1 shows:
> Empty result set
>
>
> Is this a bug of Oracle 9,2.0.1?

Yes it is a bug. See section 7.7 of the ANSI/ISO standard. Join conditions behave exactly like regular search conditions - in fact they are defined as such. The only difference is that the join condition is evaluated against a cross join so as to determine the "inner" half of the query and the "outer" part consists of those rows that don't meet the join condition. The end result is a union:

SELECT * FROM TN
UNION ALL
SELECT * FROM XN1 where TN is the restriction of the cross-product and XN1 is the "outer" or "preserved" part, extended with nulls. There is no special restriction on the type of condition that can be used as a join condition, just the obvious one that any column references are valid and within scope. It's had to summarise the full definition in a small space but that's roughly what it means.

This is one of those occassions when Oracle's (+) syntax is definitely not equivalent to an ANSI outer join - despite some common misconceptions that it is so.

-- 
David Portas