oracle exists mysql_关于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/，如需转载，请注明出处，否则将追究法律责任。