Showing posts with label differently. Show all posts
Showing posts with label differently. Show all posts

Monday, March 26, 2012

Outer join runs differently on SQL Server and Oracle

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 thinkAK 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|||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@.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@.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|||yep, one more reason to patch up:
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Thanks for the feedback