关于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://blog.itpub.net/7728585/viewspace-1588867/