in,not in,exists,not exists的区别及not exists 代替max查询效率更高

SQL中in可以分为三类：

　　1、形如select * from t1 where f1 in ('a','b')，应该和以下两种比较效率

　　select * from t1 where f1='a' or f1='b'

　　或者 select * from t1 where f1 ='a' union all select * from t1 f1='b'

　　你可能指的不是这一类，这里不做讨论。

　　2、形如select * from t1 where f1 in (select f1 from t2 where t2.fx='x')，

　　其中子查询的where里的条件不受外层查询的影响，这类查询一般情况下，自动优化会转成exist语句，也就是效率和exist一样。

　　3、形如select * from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx)，

　　其中子查询的where里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般认为效率不如exists。

　　除了第一类in语句都是可以转化成exists 语句的SQL，一般编程习惯应该是用exists而不用in，而很少去考虑in和exists的执行效率.

 

 

not exists 代替max查询效率更高

 

在同一表里查询最大值。

 

对此我记得还做过如下测试：

　　表

　　test

　　结构

　　id int identity(1,1), --id主键\自增

　　sort int, --类别,每一千条数据为一个类别

　　sid int --分类id

　　插入600w条数据

　　如果要查询每个类别的最大sid 的话

select  *  from test a 
　　 where  not exists(select  1  from test  where  sort  =  a.sort and sid  >  a.sid) 

比

select  *  from test a 
　　 where  sid  in  (select max(sid) from test  where  sort  =  a.sort) 

的执行效率要高三倍以上。

转载于:https://www.cnblogs.com/jes_shaw/archive/2010/03/05/1679117.html