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
。