oracle sql优化中not in子句包含null返回结果为空的分析

这篇文章主要介绍“oracle sql优化中not in子句包含null返回结果为空的分析”，在日常操作中，相信很多人在oracle sql优化中not in子句包含null返回结果为空的分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”oracle sql优化中not in子句包含null返回结果为空的分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

创建测试表：

create table t_dept as select * from   scott.dept;

create table t_emp as select * from   scott.emp;

insert into t_emp(deptno,ename)  values(null,MINGSHUO);   --在emp表中插入一条数据，deptno列为null

commit;

数据结构如下：

SQL> select distinct deptno from   t_emp;

      DEPTNO

----------

          30

          20

          10

SQL> select distinct deptno from   t_dept;

      DEPTNO

----------

          30

          20

          40

          10

此时发起一条查询，查询不在emp中但是在dept表中部门信息：

SQL> select * from t_dept where deptno   not in (select deptno from t_emp where deptno is not null);

DEPTNO DNAME          LOC

---------- -------------- -------------

          40 OPERATIONS     BOSTON

此时是有结果返回的。

然后把子查询中的where dept is not null去掉，再次运行查询：

SQL> select * from t_dept where deptno   not in (select deptno from t_emp);

no rows selected

此时返回结果为空。

这里很多人存在疑惑，为什么子查询结果集包括null就会出问题，比如t_dept.deptno为40的时候，40 not in （10,20,30,null）也成立啊。毕竟oracle查询优化器不如人脑智能懂得变通，查看执行计划就比较容易明白了。

Execution Plan

----------------------------------------------------------

Plan hash value: 2864198334

-----------------------------------------------------------------------------

| Id    | Operation          | Name   | Rows    | Bytes | Cost (%CPU)| Time       |

-----------------------------------------------------------------------------

|     0 | SELECT STATEMENT   |        |       4 |   172 |     5    (20)| 00:00:01 |

|*    1 |  HASH   JOIN ANTI NA |        |     4 |     172 |     5  (20)| 00:00:01 |

|     2 |   TABLE ACCESS FULL| T_DEPT   |     4 |   120 |       2   (0)| 00:00:01 |

|     3 |   TABLE ACCESS FULL|   T_EMP  |    15 |     195 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by   operation id):

---------------------------------------------------

     1 - access("DEPTNO"="DEPTNO")

Note

-----

- dynamic sampling used for this statement (level=2)

注意到这里id 1是HASH JOIN ANTI NA。这时候就想起来了，not in是对null值敏感的。所以普通反连接是不能处理null的，因此oracle推出了改良版的能处理null的反连接方法，这种方法被称为"Null-Aware Anti Join"。operation中的关键字NA就是这么来的了。

在Oracle 11gR2中，Oracl通过受隐含参数_OPTIMIZER_NULL_AWARE_ANTIJOIN控制NA，其默认值为TRUE，表示启用Null-Aware Anti Join。

下面禁用掉，然后再观察：

alter session set   "_optimizer_null_aware_antijoin" = false;

再次执行：select * from t_dept where deptno   not in (select deptno from t_emp);

执行计划如下：

Execution Plan

----------------------------------------------------------

Plan hash value: 393913035

-----------------------------------------------------------------------------

| Id    | Operation          | Name   | Rows    | Bytes | Cost (%CPU)| Time       |

-----------------------------------------------------------------------------

|     0 | SELECT STATEMENT   |        |       1 |    30 |     4     (0)| 00:00:01 |

|*    1 |  FILTER            |        |       |         |            |          |

|     2 |   TABLE ACCESS FULL| T_DEPT   |     4 |   120 |       2   (0)| 00:00:01 |

|*    3 |   TABLE ACCESS FULL|   T_EMP  |    14 |     182 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by   operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "T_EMP"   "T_EMP" WHERE

                LNNVL("DEPTNO"<>:B1)))

     3 - filter(LNNVL("DEPTNO"<>:B1))

Note

-----

     - dynamic sampling used for this statement (level=2)

lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。

此时比如t_dept.deptno为40的时候，（40 not in 10）and（40 not in 20）and（40 not in 30）and（40 not in null），注意这里是and“并且”，条件都需要满足。

结果是true and true and true and false或者unknow。经过lvnnvl函数后：

false and false and false and true,结果还是false。所以自然就不会有结果了。

如果还不明白的话换个比较直观的写法：

SQL> select * from t_dept where deptno   not in (10,20,null);

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 719542577

----------------------------------------------------------------------------

| Id    | Operation         | Name   | Rows    | Bytes | Cost (%CPU)| Time       |

----------------------------------------------------------------------------

|     0 | SELECT STATEMENT  |        |       1 |    30 |     2     (0)| 00:00:01 |

|*    1 |  TABLE ACCESS FULL| T_DEPT   |     1 |    30 |       2   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by   operation id):

---------------------------------------------------

1 - filter("DEPTNO"<>10 AND   "DEPTNO"<>20 AND

                "DEPTNO"<>TO_NUMBER(NULL))

Note

-----

     - dynamic sampling used for this statement (level=2)

过滤条件"DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>TO_NUMBER(NULL)因为最后一个and条件，整个条件恒为flase或者unkonw。

所以not in的子查询中出现null值，无返回结果。

这种时候其实可以用not exists写法和外连接代替：

not exists写法：

其实这种写法前面已经出现过了。就在禁用掉反连接之后，出现在fileter中的，oracle在内部改写sql时可能就采用了这种写法：

select *

    from t_dept d

where not exists (select 1 from t_emp e   where d.deptno = e.deptno);

外连接的写法：

select d.* from t_dept d, t_emp e where   d.deptno=e.deptno(+) and e.deptno is null;

同事还给我展示了丁俊的实验，里面有复合列的讨论，结论简单明了，这里我就直接搬过来吧，如下：

/**

根据NULL的比较和逻辑运算规则，OR条件有一个为TRUE则返回TRUE，全为FALSE则结果为FALSE，其他为UNKNOWN，比如

(1,2) not in (null,2)则相当于1 <> null or 2 <> 2,那么明显返回的结果是UNKNOWN，所以不可能为真，不返回结果，但是

(1,2) not in (null,3)相当于1 <> null or 2 <> 3,因为2<>3的已经是TRUE,所以条件为TRUE，返回结果，也就说明了为什么Q2中的

测试是那样的结果

**/

看个简单的结果：

SQL> SELECT * FROM DUAL WHERE (1,2)   not in ( (null,2) );

DUMMY

-----

SQL> SELECT * FROM DUAL WHERE (1,2)   not in ( (null,3) );

DUMMY

-----

X

到此，关于“oracle sql优化中not in子句包含null返回结果为空的分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！