Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |