Sql语句中IN和exists的区别及应用

标签: Sql in exists
18人阅读 评论(0) 收藏 举报
分类:

用EXISTS替代IN、用NOT EXISTS替代NOT IN:


在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。


  表展示

    首先,查询中涉及到的两个表,一个user和一个order表,具体表的内容如下:

    user表:

    

    order表:

    

 

  in

    确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。

    具体sql语句如下:

复制代码
 1 SELECT
 2     *
 3 FROM
 4     `user`
 5 WHERE
 6     `user`.id IN (
 7         SELECT
 8             `order`.user_id
 9         FROM
10             `order`
11     )
复制代码

    这条语句很简单,通过子查询查到的user_id 的数据,去匹配user表中的id然后得到结果。该语句执行结果如下:

    

    它的执行流程是什么样子的呢?让我们一起来看一下。

    首先,在数据库内部,查询子查询,执行如下代码:

        SELECT
            `order`.user_id
        FROM
            `order`

    执行完毕后,得到结果如下:

    

    此时,将查询到的结果和原有的user表做一个笛卡尔积,结果如下:

    

    此时,再根据我们的user.id IN order.user_id的条件,将结果进行筛选(既比较id列和user_id 列的值是否相等,将不相等的删除)。最后,得到两条符合条件的数据。
    

  exists

    指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

    具体sql语句如下:

复制代码
 1 SELECT
 2     `user`.*
 3 FROM
 4     `user`
 5 WHERE
 6     EXISTS (
 7         SELECT
 8             `order`.user_id
 9         FROM
10             `order`
11         WHERE
12             `user`.id = `order`.user_id
13     )
复制代码

    这条sql语句的执行结果和上面的in的执行结果是一样的。

    

    但是,不一样的是它们的执行流程完全不一样:

    使用exists关键字进行查询的时候,首先,我们先查询的不是子查询的内容,而是查我们的主查询的表,也就是说,我们先执行的sql语句是:

     SELECT `user`.* FROM `user` 

    得到的结果如下:

    

    然后,根据表的每一条记录,执行以下语句,依次去判断where后面的条件是否成立:

复制代码
EXISTS (
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id = `order`.user_id
    )
复制代码

    如果成立则返回true不成立则返回false。如果返回的是true的话,则该行结果保留,如果返回的是false的话,则删除该行,最后将得到的结果返回。

  区别及应用场景

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

    in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

  not in 和not exists

    如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。

查看评论

sql语句中exists和in用法的区分

初学java两个多月,这两天在整理老师的笔记对数据库中的exists和in的用法分不清楚,从网上搜了些资料,作了点总结。 一、问题起因 起初是由两条语句执行结果不同,语句如下: --分析以下结果...
  • U___U
  • U___U
  • 2015-03-27 15:25:43
  • 4974

sql中exists的用法及与in的比较

EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False 有一个查询如下: 复制代码 代码如下: SELECT c.Cu...
  • zisongjia
  • zisongjia
  • 2016-09-19 11:52:32
  • 727

SQL中EXISTS与IN

  • 2011年12月04日 19:28
  • 46KB
  • 下载

Sql语句优化之用exists、not exists替代in、not in

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下, 使用exists(或not exists)通常将提高查询的效率。在子查询中,not in子句将执行一个内部的排序...
  • wq122289014
  • wq122289014
  • 2016-10-20 17:29:02
  • 257

sql语句中的 in 、not in 、exists、not exists 详细用法说明和差别----not in失效

 2016攒课第二期之你听课我买单,快来攒你想听的课!     架构免费公开课报名     【UDN沙龙】Intel大拿分享HTML5时代的跨平台开发解 ...
  • hewusheng10
  • hewusheng10
  • 2016-04-15 11:16:43
  • 1349

sql语句中使用#跟$的区别(MyBatis中)

1、#可以进行预编译,进行类型匹配,#变量名# 会转化为 jdbc 的 类型 $不进行数据类型匹配,$变量名$就直接把 $name$替换为 name的内容 例如: select * from t...
  • Unique_YF
  • Unique_YF
  • 2017-08-30 11:22:25
  • 1109

mybatis中sql语句里面的$和#的区别

在写mybatissql语句时,都会遇到要传值的问题,那
  • u012852730
  • u012852730
  • 2014-04-10 12:00:00
  • 3333

数据库sql语句的exists使用说明

前言:EXISTS语法并没有说哪个字段落在了子查寻的结果中,而是说exists后面的语句执行的结果是不是有记录,只要有记录,则主查询语句就成立。它代表‘存在’,用来引领嵌套查询的子查询,它不返回任何数...
  • Ink4T
  • Ink4T
  • 2017-08-27 22:59:52
  • 1387

理解SQL语句中 Exists()

理解SQL语句中 Exists() exists对于主查询而言只有一个作用:返回ture或false,而其本身查询的結果集不具任何意义因此在子查询的Select命令语句的字段行中通常使用通用字符*或者...
  • zwzgood
  • zwzgood
  • 2009-06-17 09:56:00
  • 2109

in exist not_in

  • 2013年03月22日 15:35
  • 31KB
  • 下载
    个人资料
    持之以恒
    等级:
    访问量: 1万+
    积分: 396
    排名: 18万+
    文章存档
    最新评论