sql之exsists, in, all, any, some,

1. SQL中exists和in的区别 
   
   两张表——用户表TDefUser（userid，address,phone）和消费表TAccConsume(userid,time,amount)，需要查消费超过5000的用户记录。 
   用exists: 
   select * from TDefUser 
   where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000) 
   用in: 
   select * from TDefUser 
   where userid in (select userid from TAccConsume where TAccConsume.amount>5000) 
   
   
   通常情况下采用exists要比in效率高。 
   
   
   exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因，换成"select 6"完全一样，当然也可以select字段，但是明显效率低些) 
   其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询. 
   
   
   in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出. 
   
   
   比如用户表TDefUser（userid，address,phone），消费表TAccConsume(userid,time,amount)数据如下： 
   
   
   消费表聚集索引是userid,time 
   数据（注意因为有聚集索引,实际存储也是按以下次序的） 
   1    2006-1-1   200 
   1    2006-1-2   300 
   1    2006-1-2   500 
   1    2006-1-3   2000 
   1    2006-1-3   2000 
   1    2006-1-4   400 
   1    2006-1-5   500 
   2    2006-1-1   200 
   2    2006-1-2   300 
   2    2006-1-2   500 
   2    2006-1-3   2000 
   2    2006-1-3   6000 
   2    2006-1-4   400 
   2    2006-1-5   8000 
   3    2006-1-1   7000 
   3    2006-1-2   30000 
   3    2006-1-2   50000 
   3    2006-1-3   20000 
   
   
   语句： 
   select * from TDefUser 
   where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000) 
   
   
   对于userid=1，需要找所有记录，才返回false，与第二个语句的效率差不多 
   对于userid=2，找到2006-1-3的记录，就返回true，比第而个语句的效率高 
   对于userid=3，第一条记录就返回true，比第二个语句的效率高 
   
   
   语句 
   select * from TDefUser 
   where userid in (select userid from TAccConsume where TAccConsume.amount>5000) 
   
   
   返回空记录集 
   2 
   2 
   3 
   3 
   3 
   3 
   
   
   再判断 
   
   
   语句 
   select * from TDefUser 
   where userid in (select userid from TAccConsume where userid=TDefUser.userid and amount>5000) 
   
   
   对于userid=1，需要找所有记录，返回空记录集，比较判断 
   对于userid=2，需要找所有记录，返回记录集 
   2 
   2 
   ，比较判断 
   对于userid=3需要找所有记录，返回记录集 
   3 
   3 
   3 
   3 
   ，比较判断 
   
   
   表中如果没有聚集索引，对exists每个userid查找的条数都不同，但都是<=第三个语句需要扫描的条数，极端的（比如>5000的都是在最后）与第三个语句效率相似，一般的比第二个语句快，所以说,“一般”exists比in效率高 
   
   
   
   
   all, any, some的区别 
   1.All 
   
   
   对所有数据都满足条件，整个条件才成立 
   select * 
   from A 
   where 5>All(select id from A) --所有的值都要大于5 
   
   
   
   
   2.Any 
   
   
   只要有任一条数据满足条件，整个条件成立 
   select * 
   from A 
   where 3>any(select id from A) --当结果中只要有一个值小于3条件就可成立 
   go 
   3.Some和Any一样表示的限制相
   
   
   
   
2. ALL与ANY的关系就是，AND 与 OR 的关系。
   ANY与SOME等价，据说搞这两个不同的词出来是为了迁就英语语法。例如，在用= ANY 的地方在（英语）语法上就应该是= SOME。
   IN 与 = ANY 等价，均表示，变量在（子查询）列表之中，即 a IN (table B) 表示 a = ANY B.b
   NOT IN 与 <> ALL 等价，而不等于<> ANY，前两者均表示，变量不在（子查询）列表之中，即 a NOT IN (table B) 表示 a <> ALL B.b。而如果a <> ANY B.b，则只要任意一个b<>a就true了。
   IN 与 EXISTS 的性能区别主要来自，IN 会编列子查询的每行记录，然后再返回，而EXISTS 则只要遇到第一个满足条件的记录就马上返回。
   NOT IN 与 NOT EXISTS 并不能完全等价，只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时才用NOT IN。