Not in 和 not exists

fteair · 发表于 2020-6-3 17:21:13

[size=13.3333px]Not exists 的意思是，关联查询，返回除了关联子查询所得结果之外的值，

[size=13.3333px]
看如下的执行计划和代价便可以看出来。两者的差距。

SCOTT@ rac1>select * from emp where empno not in (select empno from t4 where t4.deptno=20) ;9 rows selected.Elapsed: 00:00:01.72Execution Plan----------------------------------------------------------Plan hash value: 3504968978---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 1120 | 6130 (2)| 00:00:01 ||* 1 | HASH JOIN ANTI NA | | 20 | 1120 | 6130 (2)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 20 | 600 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL| T4 | 1354K| 33M| 6120 (2)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("EMPNO"="EMPNO") 3 - filter("T4"."DEPTNO"=20)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0 recursive calls 1 db block gets 43236 consistent gets 21573 physical reads 0 redo size 1391 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9 rows processedSCOTT@ rac1>select * from emp where not exists (select empno from t4 where emp.deptno=t4.deptno and t4.deptno=20) ;9 rows selected.Elapsed: 00:00:05.45Execution Plan----------------------------------------------------------Plan hash value: 3745834269--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 860 | 2033 (98)| 00:00:01 || 1 | NESTED LOOPS ANTI | | 20 | 860 | 2033 (98)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 20 | 600 | 3 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | DEPTNOIND | 1 | 13 | 101 (99)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("T4"."DEPTNO"=20) filter("EMP"."DEPTNO"="T4"."DEPTNO")Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 7754 consistent gets 7724 physical reads 0 redo size 1374 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9 rows processed

[size=13.3333px]最后来写个总结，之前到现在一共写了三篇关于 exists 的文章，自觉有点繁琐。但也是自己不断学习的过程。很多东西还是需要自己不断的去操作，思考。总结。言归正传。

[size=13.3333px]
exists 和 in 在两张表差不多大小的情况下，效率，速度，是不会相差很大的。

[size=13.3333px]
在一大一小的情况下是存在效率上的差别的。尽管他们的执行计划有可能是相同的。 exists 更适用于子表大，in 适用于父表大。具体请看第二篇。

[size=13.3333px]
not exsits 与not in 相比，not in 之前已经看过了，并不会走相关的索引。所以，尽量使用 not exists。

[size=13.3333px]
另，这两个查询中如果有null值，会返回全部的结果集。所以注意写语句的时候尽量避开null值。