mssql sqlserver 联接中null坑


摘要:
下文讲述两张数据表之间连接出现null,无法匹配现象,如下所示:
实验环境:sql server 2008 R2


例:
我们对两张数据表进行内连接时,当列值出现null时,内连接不会将两个数据表进行连接,如下所示:

  create table [maomao365.comA]
(keyId int,info varchar(30))
 go
create table [maomao365.comB]
(keyId int,info varchar(30))
go

insert into [maomao365.comA]
(keyId,info)values(1,'A'),
(2,'AA'),(null,'AAA')
go



insert into [maomao365.comB]
(keyId,info)values(2,'B__'),
(20,'sql'),(null,'Bserver')
go

select * from [maomao365.comA] a
 inner  join 
 [maomao365.comB] b on a.keyId = b.keyId 
 
----输出-----
keyId	info	keyId	info
2	AA	2	B__


go
truncate table [maomao365.comA]
drop     table [maomao365.comA]


truncate table [maomao365.comB]
drop     table [maomao365.comB]
 


从以上的例子,我们可以看出列值为null的值没有匹配,当列值有null时,
必须将null值转换为其它值,才可以进行匹配
select * from [maomao365.comA] a
inner join
[maomao365.comB] b
on isnull(a.keyId,0) = isnull(b.keyId,0)