not in 注意实现

NOT IN 与 NOT EXISTS 并不能完全等价，只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时才用NOT IN。
<pre>
db=# DROP TABLE IF EXISTS test1;

CREATE TABLE test1(id INT, name VARCHAR(32));

DROP TABLE IF EXISTS test2;

CREATE TABLE test2(id INT, name VARCHAR(32));

INSERT INTO test1 VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D');

INSERT INTO test2 VALUES(1,'A'),(2,'B'),(3,NULL),(4,'DD');
Command OK

Command OK

Command OK

Command OK

Command OK - 4 rows affected

Command OK - 4 rows affected

db=# SELECT
t1.ID,
t1.NAME
FROM
test1 t1
WHERE
t1.NAME not in (SELECT t2.name FROM test2 t2);

<b>Empty set</b>

</pre>