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效率高