1. 首页
  2. IT资讯

关于ORACLE MYSQL NOT IN和NOT exists需要注意的 NULL值

首先说明NOT IN 和NOT EXISTS 并不完全等价 ORACLE MYSQL 都是如此
源表:
SQL> select * from testa1;
NAME                          ID
——————– ———–
gaopeng                        1
gaopeng                        2
gaopeng                        3
gaopeng                        4

SQL> select * from testb1;
NAME                          ID
——————– ———–
gaopeng                        1
gaopeng                        2
gaopeng              

因为NOT IN是对NULL 敏感的而NOT  exists却不是,所以ORACLE使用的执行计划如下:
select * from testa1 where  not exists (select * from testb1 where testa1.id=testb1.id );
NAME                          ID
——————– ———–
gaopeng                        4
gaopeng                        3

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 4225223740
—————————————————————————–
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT   |        |     4 |   152 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |        |     4 |   152 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TESTA1 |     4 |   100 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TESTB1 |     3 |    39 |     2   (0)| 00:00:01 |

普通的反连接

select * from testa1 where  testa1.id not in (select testb1.id from testb1  );

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2176127487
—————————————————————————–
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT   |        |     4 |   152 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |        |     4 |   152 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TESTA1 |     4 |   100 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TESTB1 |     3 |    39 |     2   (0)| 00:00:01 |
—————————————————————————–
11g后改良的HASH JOIN ANTI NA 连接方式如果我们改变优化器到9I其执行计划为

select /*+ optimizer_features_enable(‘9.2.0’) */ * from testa1 where  testa1.id not in (select testb1.id from testb1  );
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1097631637
————————————————————-
| Id  | Operation          | Name   | Rows  | Bytes | Cost  |
————————————————————-
|    0 | SELECT STATEMENT   |        |     4 |   100 |     4 |
|*   1 |  FILTER            |        |       |       |       |
|     2 |   TABLE ACCESS FULL| TESTA1 |     4 |   100 |     2 |
|*   3 |   TABLE ACCESS FULL| TESTB1 |    78 |  1014 |     2 |
————————————————————-
可以看到老的执行计划是用不了的。

关于MYSQL 我也实验了一样和ORACLE一样 其执行计划如下:
mysql> explain select * from testa1 where  testa1.id not in (select testb1.id from testb1  );
+—-+————-+——–+——+—————+——+———+——+——+————-+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+—-+————-+——–+——+—————+——+———+——+——+————-+
|  1 | PRIMARY     | testa1 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
|  2 | SUBQUERY    | testb1 | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL        |
+—-+————-+——–+——+—————+——+———+——+——+————-+
2 rows in set (0.00 sec)

mysql> explain select * from testa1 where  not exists (select * from testb1 where testa1.id=testb1.id );
+—-+——————–+——–+——+—————+——+———+——+——+————-+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+—-+——————–+——–+——+—————+——+———+——+——+————-+
|  1 | PRIMARY            | testa1 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
|  2 | DEPENDENT SUBQUERY | testb1 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+—-+——————–+——–+——+—————+——+———+——+——+————-+

当然 in 和 exists 是等价的

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-1588867/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183800.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code