in和exists
假如有两张表,一张user表,一张order表
user表
------------------------------------------
id | username | sex | address
------------------------------------------
1 | xxxxxxxx | xxx | xxxxxxx
2 | xxxxxxxx | xxx | xxxxxxx
4 | xxxxxxxx | xxx | xxxxxxx
------------------------------------------
order表
----------------------------------
id | user_id | order_sn
----------------------------------
1 | 1 | xxxxxxxx
2 | 2 | xxxxxxxx
3 | 1 | xxxxxxxx
----------------------------------
现要查询user表中用户下过订单的用户信息
in
- in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。
- 具体的sql语句如下
select
user.*
from
user
where
user.id in (select order.user_id from order)
- in的查询顺序
- 先查询内表的结果
select order.user_id from order
- 再去匹配外表,匹配成功则返回结果
- 先查询内表的结果
- in 查询是先查询子查询的结果,然后去匹配user表中的id,所以当内表即order表比较小时,in的速度比较快
exists
- 指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
- 具体的sql语句如下
select
user.*
from
user
where
exists(select order.user_id from order where user.id=order.user_id)
- exists查询顺序
- 先查询外表的结果
select user.* from user
- 然后每一条记录再去匹配where子句,看是否成立,如果成立则返回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要快。