30
2016
11

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()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

但是用到exists时,它后面的子查询只需要返回一个boolean结果,它不能那样写 exists('1','2','3')

小结:当查询需要有明确的值时,用in,反之则用exists

对于in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化 的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索 引及结果集的关系了 ,另外IN时不对NULL进行处理。

现在网上对EXIST和IN的效率谁比较高一直争论不休,其实这个问题我们本身也很难判断,毕竟有时候效率这个问题不是我们轻易能看出来的。

EXISTS、IN与JOIN,都可以用来实现形如“查询A表中在(或不在)B表中的记录”的查询逻辑。

在查询的两个表大小相当的情况下,3种查询方式的执行时间通常是:
EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时,NOT IN的方式最慢:
NOT EXISTS <= LEFT JOIN <= NOT IN

但是如果两个表中一个较小,一个较大,则子查询表大的用exists,子查询表小的用in,因为in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。而无论那个表大,用not exists都比not in要快。这是因为如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。

IN的好处是逻辑直观简单(通常是独立子查询);缺点是只能判断单字段,并且当NOT IN时效率较低,而且NULL会导致不想要的结果。
EXISTS的好处是效率高,可以判断单字段和组合字段,并不受NULL的影响;缺点是逻辑稍微复杂(通常是相关子查询)。
JOIN用在这种场合,往往是吃力不讨好。JOIN的用途是联接两个表,而不是判断一个表的记录是否在另一个表。

« 上一篇 下一篇 »