NOT EXISTS不等于NOT IN，IN等于EXISTS

IN （select id from a ） 等价于EXISTS（select 1 from a where id = outer.id）。

而NOT IN只在两个表的两个字段上面都不存在NULL值的时候才等价。

当一个集合中存在NULL值的时候，NOT IN的结果是UNKNOW。判断一个NULL值在一个集合中是否存在，结果同样是UNKNOW。

mysql> select * from t1 ;
+------+--------+
| id   | name   |
+------+--------+
| a    | sfd    |
| b    | were   |
| c    | erwe   |
| NULL | sere   |
| e    | erewer |
+------+--------+
5 rows in set (0.00 sec)
 
mysql> select * from t2 ;
+------+
| id   |
+------+
| a    |
| b    |
| c    |
| NULL |
+------+
4 rows in set (0.00 sec)
 
mysql> select * from t1 where id in ( select * from t2 ) ;
+------+------+
| id   | name |
+------+------+
| a    | sfd  |
| b    | were |
| c    | erwe |
+------+------+
3 rows in set (0.00 sec)
 
mysql> select * from t1 where exists ( select 1 from t2 where t1.id = t2.id ) ;
+------+------+
| id   | name |
+------+------+
| a    | sfd  |
| b    | were |
| c    | erwe |
+------+------+
3 rows in set (0.00 sec)
 
mysql> select * from t1 where id not in ( select * from t2 ) ;
Empty set (0.00 sec)
 
mysql> select * from t1 where not exists ( select 1 from t2 where t1.id = t2.id ) ;
+------+--------+
| id   | name   |
+------+--------+
| NULL | sere   |
| e    | erewer |
+------+--------+
2 rows in set (0.00 sec)