Re: Outer join runs differently on SQL Server and Oracle

From: <fitzjarrell_at_cox.net>
Date: 11 Aug 2005 10:19:22 -0700
Message-ID: <1123780762.225338.310790@g14g2000cwa.googlegroups.com>

AK wrote:
> 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

I think you're using an unpatched release of Oracle 9i, whichever release that may be (9i says NOTHING about which release or patch level you're using):

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit 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>
SQL> insert into t values(1);

1 row created.

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

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

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

         1

SQL> The results look exactly the same to me between the two. Care to try again, this time with a patched release of Oracle?

David Fitzjarrell Received on Thu Aug 11 2005 - 12:19:22 CDT