oracle in非常规写法

正常情况下.in里面接的是一个完整的子查询,这里如果嵌入外表的话:CBO底层会做查询转换

环境:12C

-1.in的情况
select * from dept a where a.dname  in (select b.dname from dept b where a.deptno<>b.deptno) 
这里的含义:2表关联后,找a.dname=b.dname的数据;

此时子查询会展开
等价:
select * from dept a, dept b
 where a.dname=b.dname
 and a.deptno<>b.deptno;
 
 
 Plan Hash Value  : 3116701014 

----------------------------------------------------------------------
| Id  | Operation            | Name | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    9 |   288 |    6 | 00:00:01 |
| * 1 |   HASH JOIN SEMI     |      |    9 |   288 |    6 | 00:00:01 |
|   2 |    TABLE ACCESS FULL | DEPT |    9 |   180 |    3 | 00:00:01 |
|   3 |    TABLE ACCESS FULL | DEPT |    9 |   108 |    3 | 00:00:01 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."DNAME"="B"."DNAME")
* 1 - filter("A"."DEPTNO"<>"B"."DEPTNO")


2.not in的情况
select * from dept a where a.dname not in (select dname from dept b where a.deptno<>b.deptno) 
含义是:
2表关联后,找a.dname<>b.dname的数据;


这里子查询没有展开
于是cbo查询转换为 not EXISTS,但是not in跟not EXISTS是有区别的,在于not in对null值判断结果为空,所以在转换
为not EXISTS的时候,需要保证dname的值存在null时,结果为空
于是等价为
select * from dept a where not EXISTS (select dname from dept b where a.deptno<>b.deptno and (dname is null or a.dname is null or dname=a.dname ))

简化:
select * from dept a where not EXISTS (select dname from dept b where a.deptno<>b.deptno and LNNVL(dname<>a.dname))

通过下面的执行机会即可证明:
 Plan Hash Value  : 4041342997 
----------------------------------------------------------------------
| Id  | Operation            | Name | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    8 |   160 |   17 | 00:00:01 |
| * 1 |   FILTER             |      |      |       |      |          |
|   2 |    TABLE ACCESS FULL | DEPT |    9 |   180 |    3 | 00:00:01 |
| * 3 |    TABLE ACCESS FULL | DEPT |    1 |    12 |    3 | 00:00:01 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter( NOT EXISTS (SELECT 0 FROM "DEPT" "B" WHERE "B"."DEPTNO"<>:B1 AND LNNVL("DNAME"<>:B2)))
* 3 - filter("B"."DEPTNO"<>:B1 AND LNNVL("DNAME"<>:B2))



为了使你更好的理解,请看直接使用not EXISTS的时候的执行计划
select * from dept a where not EXISTS (select dname from dept b where a.deptno<>b.deptno)
Plan Hash Value  : 889855946 
-----------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    8 |   160 |    8 | 00:00:01 |
| * 1 |   FILTER             |             |      |       |      |          |
|   2 |    TABLE ACCESS FULL | DEPT        |    9 |   180 |    3 | 00:00:01 |
| * 3 |    INDEX FULL SCAN   | SYS_C007350 |    1 |     3 |    1 | 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter( NOT EXISTS (SELECT 0 FROM "DEPT" "B" WHERE "B"."DEPTNO"<>:B1))
* 3 - filter("B"."DEPTNO"<>:B1)