sybase的外连接

跟oracle的有不少区别.

今天做了一下实验,发现sybase的外连接跟oracle的大不一样.

还是没有能够吃透sybase.

Oracle 8.1.7下:

drop table GK_ZJZFSQ_DETAIL
/
create table GK_ZJZFSQ_DETAIL
(
GK_ZFPZ_ID int not null
)
/

insert into GK_ZJZFSQ_DETAIL
values(76)
/
insert into GK_ZJZFSQ_DETAIL
values(0)
/

drop table GK_SQZFPZ
/
create table GK_SQZFPZ (
GK_ZFPZ_ID int not null,
CHECK_STATUS varchar(2) default '0' null
)
/
insert into GK_SQZFPZ values(76,'3')
/

commit
/

执行的sql:

select t1.GK_ZFPZ_ID,t3.CHECK_STATUS
from GK_ZJZFSQ_DETAIL t1,GK_SQZFPZ t3
where t1.GK_ZFPZ_ID = t3.GK_ZFPZ_ID(+)
/

select t1.GK_ZFPZ_ID,t3.CHECK_STATUS
from GK_ZJZFSQ_DETAIL t1,GK_SQZFPZ t3
where t1.GK_ZFPZ_ID = t3.GK_ZFPZ_ID(+)
and nvl(t3.CHECK_STATUS,'0') not in ('2','3')
/

结果集分别是:

GK_ZFPZ_ID CH
---------- --
0
76 3

和

GK_ZFPZ_ID CH
---------- --
0

而在sybase下
select t1.GK_ZFPZ_ID,t3.CHECK_STATUS
from GK_ZJZFSQ_DETAIL t1,GK_SQZFPZ t3
where t1.GK_ZFPZ_ID *= t3.GK_ZFPZ_ID
go

select t1.GK_ZFPZ_ID,t3.CHECK_STATUS
from GK_ZJZFSQ_DETAIL t1,GK_SQZFPZ t3
where t1.GK_ZFPZ_ID *= t3.GK_ZFPZ_ID
and isnull(t3.CHECK_STATUS,'0') not in ('2','3')
go

的结果集分别是:

76 3
0 null
和

76 null
0 null

大不一样.