关于用not in与not exists查询结果不一样的问题

[chenjiong]

用
SELECT 小组编码,小组名称
FROM 小组表 where not exists (select * from 登记表 where 登记表.小组=小组表.小组编码)

查询出5条记录。这个结果对的，小组表的确有5条记录是登记表中的小组 所没有的。


而用
SELECT 小组编码,小组名称
FROM 小组表 where 小组编码 not in (select 小组 from 登记表)

查询的结果是0条。


这二者有什么不同吗？注：登记表中的小组字段不是唯一的，有重复而且很多。


另外，在sqlserver2005中，似乎in 与exists的效率相当，是有优化？

[叶子]

我觉得exists 比 in的效率好点。

[中国风]

select 小组 from 选民登记表--有(小组=null)時，會出現0記錄，null可用not exists方法

[中国风]

在sqlserver2005中，似乎in 与exists的效率相当，是有优化

查看執行計劃

[chenjiong]

[Quote=引用 2 楼 roy_88 的回复:]
select 小组 from 选民登记表--有(小组=null)時，會出現0記錄，null可用not exists方法
[/Quote]

应该是这个原因，找来找去就是这原因。谢谢。

[叶子]

declare @小组表 table (小组编码 int,小组名称 varchar(1))
insert into @小组表
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f' union all
select 7,'g' union all
select 8,'h' union all
select 9,'i' union all
select 10,'j'

declare @登记表 table (小组 int)
insert into @登记表
select 1 union all
select 3 union all
select 4 union all
select 5 union all
select 6

SELECT 小组编码,小组名称
FROM @小组表 a where 
not exists (select * from @登记表 b where b.小组=a.小组编码)

/*
小组编码        小组名称
----------- ----
2           b
7           g
8           h
9           i
10          j
*/

SELECT 小组编码,小组名称
FROM @小组表 where 小组编码 not in (select 小组 from @登记表)
/*
小组编码        小组名称
----------- ----
2           b
7           g
8           h
9           i
10          j
*/

本地测试结果相同

[叶子]

declare @小组表 table (小组编码 int,小组名称 varchar(1))
insert into @小组表
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select null,null union all
select 7,'g' union all
select 8,'h' union all
select 9,'i' union all
select 10,'j'

declare @登记表 table (小组 int)
insert into @登记表
select null union all
select 3 union all
select 4 union all
select 5 union all
select 6

SELECT 小组编码,小组名称
FROM @小组表 a where 
not exists (select * from @登记表 b where b.小组=a.小组编码)

/*
小组编码        小组名称
----------- ----
2           b
7           g
8           h
9           i
10          j
*/

SELECT 小组编码,小组名称
FROM @小组表 where 小组编码 not in (select 小组 from @登记表)
/*
小组编码        小组名称
----------- ----

(0 row(s) affected)
*/

[chenjiong]

刚刚测试的结果是not in稍慢些点点，not in的reads是41，而not exists是38

登记表记录12万，小组表记录30条不到。

以后再试试两个大表，可能两大表的时候，exists的效率才会体现