Oracle的反连接中null值问题？

原创张鹏 2021-09-23

397

测试准备：

create table t1(col1 number,col2 varchar2(1));
create table t2(col2 varchar2(1),col3 varchar2(2));
insert into t1 values(1,'A');
insert into t1 values(2,'B');
insert into t1 values(3,'C');
insert into t2 values('A','A2');
insert into t2 values('B','B2');
insert into t2 values('D','D2');
commit;
 
SQL> set autot on
SQL> select * from t1 where col2 not in (select col2 from t2);
 
      COL1 C
---------- -
         3 C
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    51 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     3 |    51 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    45 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where col2 <> all (select col2 from t2);
       COL1 C
---------- -
         3 C
 Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    51 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     3 |    51 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    45 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where not exists (select 1 from t2 where col2=t1.col2);
       COL1 C
---------- -
         3 C
 Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    51 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |    51 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    45 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>

如果T1表中col2有null值：

insert into t1(col1) values('4');
commit;
 SQL> select * from t1 where col2 not in (select col2 from t2);
       COL1 C
---------- -
         3 C
 Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    68 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     4 |    68 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |    60 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where col2 <> all (select col2 from t2);
       COL1 C
---------- -
         3 C
 Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    68 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     4 |    68 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |    60 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where not exists (select 1 from t2 where col2=t1.col2);
       COL1 C
---------- -
         4
         3 C
 Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    68 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     4 |    68 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |    60 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>

如果T2中col2有null值：

delete from t1 where col1=4;
insert into t2(col3) values('E2');
commit;
 SQL> select * from t1 where col2 not in (select col2 from t2);
 no rows selected
 Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    51 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     3 |    51 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    45 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where col2 <> all (select col2 from t2);
 no rows selected
 Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    51 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     3 |    51 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    45 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where not exists (select 1 from t2 where col2=t1.col2);
       COL1 C
---------- -
         3 C
 Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    51 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |    51 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    45 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>

not in、<> all对null值敏感，即not in、、<> all后面的子查询或者常亮集合一旦有null值出现，整个sql的执行结果就为null。
not exists对null值不敏感，即null值对执行结果不会有什么影响。

oracle

最后修改时间：2021-09-23 15:01:01

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle的反连接中null值问题？

评论