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

大不一樣.