代码改变世界

NULL对反连接的影响

  abce  阅读(283)  评论(0)  编辑  收藏  举报

测试准备：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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值：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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值：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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值对执行结果不会有什么影响。

编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
历史上的今天：
2015-10-08 RHCE7 管理II-5管理进程的优先级
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛