Re: Left join bug?

On Aug 3, 10:01 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> "joe" <fischauto..._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

besades the fact that i don't know SQL standard i'm a bit confused: On 10.2.0.2
i've :
SQL> select * from a full outer join b on (1=1);

       NUM        NUM
       ---------- ----------
         1          2
SQL>  select * from a full outer join b on (1=0);
       NUM        NUM
       ---------- ----------
         1
                    2

SQL> select * from a,b;

       NUM        NUM
       ---------- ----------
         1          2

Is this correct??

Regards

--
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com