1、in和exists
in是把外表和內(nèi)表作hash連接，而exists是對外表作loop循環(huán)，每次loop循環(huán)再對內(nèi)表進(jìn)行查詢。
eg：A(小表)，B(大表)
子查詢表小的用in，子查詢表大的用exists。
select * from A where cc in(select cc from B)
-->效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
-->效率高，用到了B表上cc列的索引。
同時(shí)：
select * from B where cc in(select cc from A)
-->效率高，用到了B表上cc列的索引
select * from B where exists(select cc from A where cc=B.cc)
-->效率低，用到了A表上cc列的索引。
2、not in 和 not exists
not in 邏輯上不完全等同于not exists，
not in 只有當(dāng)子查詢中，select 關(guān)鍵字后的字段有not null約束或者有這種暗示時(shí)用not in,另外如果主查詢中表大，子查詢中的表小但是記錄多，則應(yīng)當(dāng)使用not in,并使用anti hash join.
如果主查詢表中記錄少，子查詢表中記錄多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外連接+is null
eg：查詢在a表但不在b表的id
b:
方法一：左查詢
select a.id,a.name from a left join b on a.id=b.id where b.id is null;
方法二：not in
select a.id,a.name from a where a.id not in (select id from b);
方法三：not exists
select * from a where not exists (select * from b where a.id=b.id);
select * from a where (select count(1) from b where a.id=b.id)=0;
方法二和方法三的區(qū)別：
方法二中的not in，會(huì)調(diào)用子查詢，不會(huì)返回null值，即a表有null但b表沒有，但是并不會(huì)返回出來，而方法三會(huì)有這個(gè)null值。
再者，如果b表中有null值，通過not in 來查詢不在b表但在a表中的數(shù)據(jù)，不會(huì)返回任何值。
eg：
d:
not in：
select * from c where c2 not in (select c2 from d);
not exists:
select * from c where not exists (select c2 from d where c.c2=d.c2);
通上，如果子查詢字段有非空限制，這時(shí)可以使用not in，并且可以通過提示讓它用hasg_aj或merge_aj連接。
如果查詢語句使用了not in，那么對內(nèi)外表都進(jìn)行全表掃描，沒有用到索引；而not exists的子查詢依然能用到表上的索引。所以無論哪個(gè)表大，用not exists都比not in 要快。
參考自：https://blog.csdn.net/baidu_37107022/article/details/77278381
