in和exists
首先,看两个例子1) select * from A where A.id in(select id from B)
2) select * from A where exists(select 1 from B where id=A.id)
先说说效率,当A表的数据远远大于B表的时候,则1)句效率比较高;反之,当B表的数据远远大于A表的时候,则2)句效率表较高;
这是因为,in是把外表和内标当做hash join,而exists是对外表做loop,每次loop再对内表进行查询:
再回到前面说的例子,当使用in时,先去查出B表中的数据,然后保存在缓存中,再去查出A表中满足的数据,因为要遍历整个B表,所以适合B表数据比较少的查询;
而exists(×××)只在乎括号里的数据能不能查出来,如果存在,则2)句的where语句成立,所以exists先要查出A表中的所有数据,再去在子查询中看是否满足,所以exists要遍历整个A表,则适合A表数据比较少的查询。
当两个表的数据差不多时,in和exists的查询效率是差不多的。
not in和not exists
not in在逻辑上并不完全等于not exists,如果误用not in有可能会导致致命的BUG
看一个例子
A id name B id name
1 a 1 a
2 b c
3 c 2 c
1)select * from A where A.id not in(select id from B)
no rows found
2)select * from A where not exists(select id from B where id=A.id)
id name
1 a
2 b
正如上面所看到的的,not in出现了不期望的结果集。这是因为使用not in,如果子查询中返回任意一条记录中含有空值,则查询不返回任何记录,所以除非有限制不能有空值,尽量用not exists;
还有一个区别,使用not in,内外表都会进行全表扫描,不会使用索引,而not exists会用到子查询的索引,所以无论哪个表大,not exists的效率都会比not in要快。
因此,尽量不要使用not in。