關於ORACLE MYSQL NOT IN和NOT exists需要注意的 NULL值

因為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 是等價的