Oracle的反连接中null值问题

博主： Tehran
发布时间：2021 年 10 月 02 日
433 次浏览
暂无评论
8296字数
分类： Oracle

测试准备：

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值对执行结果不会有什么影响。**

最后修改：2021 年 10 月 02 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Oracle的反连接中null值问题

Tehran • 2021 年 10 月 02 日

<div class="tip share">请注意，本文编写于 936 天前，最后修改于 936 天前，其中某些信息可能已经过时。</div>

<h2>测试准备：</h2><pre><div class="hljs"><code class="lang-sql">create table t1(col1 number,col2 varchar2(1));
create table t2(col2 varchar2(1),col3 varchar2(2));
insert into t1 values(1,&#39;A&#39;);
insert into t1 values(2,&#39;B&#39;);
insert into t1 values(3,&#39;C&#39;);
insert into t2 values(&#39;A&#39;,&#39;A2&#39;);
insert into t2 values(&#39;B&#39;,&#39;B2&#39;);
insert into t2 values(&#39;D&#39;,&#39;D2&#39;);
commit;
 
SQL&gt; set autot on
SQL&gt; 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&gt; select * from t1 where col2 &lt;&gt; 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&gt; 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&gt;
</code></div></pre><h2>如果T1表中col2有null值：</h2><pre><div class="hljs"><code class="lang-sql">insert into t1(col1) values(&#39;4&#39;);
commit;
 SQL&gt; 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&gt; select * from t1 where col2 &lt;&gt; 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&gt; 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&gt;
</code></div></pre><h2>如果T2中col2有null值：</h2><pre><div class="hljs"><code class="lang-sql">delete from t1 where col1=4;
insert into t2(col3) values(&#39;E2&#39;);
commit;
 SQL&gt; 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&gt; select * from t1 where col2 &lt;&gt; 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&gt; 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&gt;
</code></div></pre><h2>结论</h2>**not in、&lt;&gt; all对null值敏感，即not in、、&lt;&gt; all后面的子查询或者常亮集合一旦有null值出现， 整个sql的执行结果就为null。 not exists对null值不敏感，即null值对执行结果不会有什么影响。**

Oracle的反连接中null值问题

测试准备：

如果T1表中col2有null值：

如果T2中col2有null值：

结论

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

影视资源站接口

oracle 日期的常用用法

应用层、表示层、会话层、传输层、网络层、数据链路层、物理层

有些事情，一辈子坚持那么一次，就已经足够了 .

MySQL sql语句大全

MySQL清理binlog日志

Oracle的反连接中null值问题

测试准备：

如果T1表中col2有null值：

如果T2中col2有null值：

结论

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

Oracle的反连接中null值问题

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复