IN 与 EXISTS 详解

IN 与 EXISTS 详解

IN 语句：只执行一次

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

EXISTS 语句

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

区别及应用场景

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

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

not in 和 not exists

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

示例查询

使用 EXISTS 查询分数大于 90 且课程编号为 1 的学生信息：

SELECT [学号]
      ,[姓名]
      ,[性别]
      ,[出生日期]
      ,[民族]
      ,[所属班级]
      ,[家庭住址]
  FROM [学生成绩管理系统].[dbo].[学生信息] as s 
  WHERE EXISTS (
	  SELECT *
	  FROM [学生成绩管理系统].[dbo].[成绩信息] as c 
	  WHERE c.分数 > 90 AND c.学生编号 = s.学号 AND c.课程编号 = 1
  )
GOCode language: JavaScript (javascript)

EXISTS 子查询返回 true 的示例：

SELECT *
FROM [dbo].[学生信息] s
WHERE EXISTS (
    -- exists 只要括号里面的结果有至少一行就认为是 true，类似 1=1
    -- select 1 
    -- select * from 班级信息 where 1=1
    -- select * from dbo.Test where 1=1
    SELECT * FROM dbo.班级信息 s2 
    WHERE s2.班级编号 = s.所属班级
)Code language: JavaScript (javascript)