Re: Outer join runs differently on SQL Server and Oracle

AK schrieb:
> Oracle 9i:
>
> create table t(i integer)
> Table created
>
> insert into t values(1)
> 1 row inserted
>
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> --- I beleive this is wrong
>
> drop table t
> Table dropped
>
> the same query against MS SQL Server 2000:
>
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
>
> i1 i2
> ----------- -----------
> 1 NULL
> ------- I think this is correct
>
> (1 row(s) affected)
>
> drop table t
>
> What do you think
>

Oracle ansi join implementation had some bugs ( much of them fixed in recent versions/patches ), but in your case i definitely can't reproduce your behaviour ( both on 9iR2 and 10gR1/R2)

oracle_at_col-fc1-02:~/sql >sqlplus scott/tiger

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 11 19:19:29 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> create table t(i integer);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select t1.i i1,t2.i i2

   2 from t t1 left join t t2 on 1=0;

         I1 I2
---------- ----------

          1

SQL> Here is output for 10gR1

oracle_at_wks01:~> sqlplus scott/tiger

SQL*Plus: Release 10.1.0.3.0 - Production on Do Aug 11 18:05:22 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create table t(i integer);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select t1.i i1,t2.i i2

   2 from t t1 left join t t2 on 1=0
   3 /

         I1 I2
---------- ----------

          1

SQL> Best regards

Maxim Received on Thu Aug 11 2005 - 12:25:24 CDT