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列的索引。
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 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 name from student where name in('zhang','wang','zhao');
select name from student where name='zhang' or name='wang' or name='zhao'