Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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 PortasReceived on Fri Aug 03 2007 - 15:01:21 CDT
![]() |
![]() |