一般来说,这两个是用来做两张(或更多)表联合查询用的,in是把外表和内表作hash 连接,而exists 是对外表作loop 循环,假设有A、B两个表,使用时是这样的:
1、select * from A where id in (select id from B)--使用in
2、select * from A where exists(select B.id from B where B.id=A.id)--使用exists
也可以完全不使用in和exists:
3、select A.* from A,B where A.id=B.id--不使用in和exists
具体使用时到底选择哪一个,主要考虑查询效率问题:
第一条语句使用了A表的索引;
第二条语句使用了B表的索引;
第三条语句同时使用了A表、B表的索引;
如果A、B表的数据量不大,那么这三个语句执行效率几乎无差别;
如果A表大,B表小,显然第一条语句效率更高,反之,则第二条语句效率更高;
第三条语句尽管同时使用了A表、B表的索引,单扫描次数是笛卡尔乘积,效率最差。
一直以来,大家认为exists比in速度快,其实是不准确的。且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。
如果两张表大小差不多,那么exists和in的效率差不多。
例如: 一张数据较大表为A,一张较小表B
一、
select * from A where mm in (select mm from B)
效率较高,这里用到的是大表A上的索引
select * from B exists (select mm from A where mm=B.mm)
效率较高,这里用到的是小表B上的索引
二、
select * from B where mm in (select mm from A)
效率较低,这里用到的是小表B上的索引
select * from A exists (select mm from B where mm=A.mm)
效率较高,这里用到的是大表A上的索引
三、
not exists 在使用时依然会用到表上的索引,但是not in会进行全盘扫描
因此,not exists 始终比not in 的效率高
四、
in与==效果是相同的