SQL Server 【优化】in & exists & not in & not exists

in && exists

如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

例如：表A(小表)，表B(大表)

select * from A where cc in(select cc from B)　　-->效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)　　-->效率高，用到了B表上cc列的索引。

select * from B where cc in(select cc from A)　　-->效率高，用到了B表上cc列的索引

select * from B where exists(select cc from A where cc=B.cc)　　-->效率低，用到了A表上cc列的索引。

not in && not exists

使用上的区别

• 对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。

• 对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

create table #t1(c1 int,c2 int);
create table #t2(c1 int,c2 int);
insert into #t1 values(1,2);
insert into #t1 values(1,3);
insert into #t2 values(1,2);
insert into #t2 values(1,null);

select * from #t1
select * from #t2

select * from #t1 where c2 not in(select c2 from #t2)-->执行结果：无
select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)--执行结果：1　3

优化查询

select * from T_A a (nolock) where a.xh not in (select xh from T_B)

--优化1
select * from T_A a (nolock) where not exists (select xh from T_B where xh=a.xh)

--优化2
select * from T_A a (nolock) left join T_B b on b.xh=a.xh where b.xh is null