exists 和 in 的执行原理及使用场景

港控/mmm° 2023-10-03 11:13 23阅读 0赞

## 1、exists的执行原理： ##

对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），**故内表大的使用exists，可加快效率**；

## 2、in的执行原理 ##

是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故**外表大的使用in，可加快效率。**

如果用not in ，则是内外表都全表扫描，无索引，效率低，可考虑使用not exists，也可使用A left join B on A.id=B.id where B.id is null 进行优化。

## 3、NOT EXISTS和NOT IN的区别 ##

（1）对于NOT EXISTS查询，内表存在空值对查询结果没有影响；对于NOT IN查询，内表存在空值将导致最终的查询结果为空。  
2）对于NOT EXISTS查询，外表存在空值，存在空值的那条记录最终会输出；对于NOT IN查询，外表存在空值，存在空值的那条记录，最终将被过滤，其他数据不受影响。

Not exists 会使用索引，Not in会使查询条件字段索引失效。

## 3、使用场景说明： ##

SELECT c.CustomerId, CompanyName  
        FROM Customers c  
        WHERE EXISTS(  
            SELECT OrderID FROM Orders o  
            WHERE o.CustomerID = cu.CustomerID)

分析：

> 这里使用exists的原因是，订单表里面可能记录很大，而客户表是一个相对较小的表，这样查询的话 是一种优化方式。
> 
> `exists` 语句中只要返回`true or false`就可以了，所以可以直接`select OrderID`或者`select 1。`

SELECT o.*  
        FROM Orders o  
        WHERE o.CustomerId in(1,2,3)

分析 ：这里我只查找客户编号是123的人的订单信息. in就特别合适了。

## 4、总结 ##

一直以来认为`exists`比`in`效率高的说法是不准确的。  
如果查询的两个表大小相当，那么用`in`和`exists`差别不大。  
如果两个表中一个较小，一个是大表，则子查询表大的用`exists`，子查询表小的用`in`。