in和exists、not in 和 not exists

1、in和exists
in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
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列的索引。
同时：
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 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.
如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null

eg：查询在a表但不在b表的id

a: image.png
b: image.png

方法一：左查询

select a.id,a.name from a left join b on a.id=b.id where b.id is null;

image.png

方法二：not in

select a.id,a.name from a where a.id not in (select id from b);

image.png

方法三：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;

image.png

方法二和方法三的区别：
方法二中的not in，会调用子查询，不会返回null值，即a表有null但b表没有，但是并不会返回出来，而方法三会有这个null值。
再者，如果b表中有null值，通过not in 来查询不在b表但在a表中的数据，不会返回任何值。
eg：

c: image.png
d: image.png

not in：

select * from c where c2 not in (select c2 from d);

image.png

not exists:

select * from c where not exists (select c2 from d where c.c2=d.c2);

image.png

通上，如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。
如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

参考自：https://blog.csdn.net/baidu_37107022/article/details/77278381