in he not in 有什么区别吗?
我有两张一模一样的表表A 表B
表A的CODE唯一,表B的code唯一
表A有900条数据,表B有600条数据我select * from a where code in(select code from b) 结果有600条
可是当我select * from a where code not in(select code from b)的时候,结果都是0
那我那300条数据丢哪?
我有两张一模一样的表表A 表B
表A的CODE唯一,表B的code唯一
表A有900条数据,表B有600条数据我select * from a where code in(select code from b) 结果有600条
可是当我select * from a where code not in(select code from b)的时候,结果都是0
那我那300条数据丢哪?
先检查一下a,bcode相同的数据数目了
insert into @tablea
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9declare @tableb table(id int)insert into @tableb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
select * from @tablea where id in(select id from @tableb) select * from @tablea where id not in(select id from @tableb)
/*
id
-----------
1
2
3
4
5
6
id
-----------
7
8
9*/
--建议你执行下面语句看下这个A表中code是否唯一
select code from A group by code having count(1)>1
select * from a t where not exists(select 1 from b where t.code=b.code)
select * from a where code not in (select code from b)返回的结果是0条,理论上应该是300对不?
我group 过了,的确是唯一的
出来了,是300条,为啥NOT IN 就不行呢?
表B里面的确有部分数据是NULL的,刚才topest0302老大说group 我只做了表A的,
DECLARE @I INT
SELECT 1 WHERE @I=0 OR @I<>0
--无返回记录
正常数据了,b里的a都有
NULL值引起的,请用下面的查询试试select * from a where code in(select isnull(code,'') from b)
select * from a where code not in(select isnull(code,'') from b)