怀疑 B 表的 X 字段有空值 NULLdeclare @A table(id int) insert into @A values(1) insert into @A values(2) insert into @A values(3) insert into @A values(4) declare @B table(id int) insert into @B values(1) insert into @B values(null) insert into @B values(3)select * from @A where id not in(select id from @B)
if object_id('t1') is not null drop table t1 go if object_id('t2') is not null drop table t2 go create table t1(iUserId nvarchar(10),cLoginName nvarchar(10),cUserName nvarchar(10)) insert t1 select '1', '老史', '老史' union all select '2', '老马', '老马' union all select '3', '老林', '老林' union all select '4', 'Happy', 'Stone' go create table t2(iUserId nvarchar(10),cLoginName nvarchar(10),cUserName nvarchar(10)) insert t2 select null, '老史', '老史' union all select '2', '老马', '老马' union all select null, '老林', null union all select '3', '马云', 'Stone' select * from t1 where not exists (select 1 from t2 where t1.iuserid=t2.iuserid)
insert into @A values(1)
insert into @A values(2)
insert into @A values(3)
insert into @A values(4)
declare @B table(id int)
insert into @B values(1)
insert into @B values(null)
insert into @B values(3)select * from @A where id not in(select id from @B)
drop table t1
go
if object_id('t2') is not null
drop table t2
go
create table t1(iUserId nvarchar(10),cLoginName nvarchar(10),cUserName nvarchar(10))
insert t1 select
'1', '老史', '老史' union all select
'2', '老马', '老马' union all select
'3', '老林', '老林' union all select
'4', 'Happy', 'Stone'
go
create table t2(iUserId nvarchar(10),cLoginName nvarchar(10),cUserName nvarchar(10))
insert t2 select
null, '老史', '老史' union all select
'2', '老马', '老马' union all select
null, '老林', null union all select
'3', '马云', 'Stone'
select * from t1 where not exists (select 1 from t2 where t1.iuserid=t2.iuserid)
的情况