注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

SQL中exists和in的区别 (转)  

2008-12-19 18:15:39|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

假设如下应用:
两张表——用户表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效率高

  评论这张
 
阅读(485)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016