使用not in 时，要注意null值

当查询的列中有null值时，不能使用not in

替换成 not exists 和关联子查询

CREATE TABLE new_dept(deptno INT);

INSERT INTO new_dept VALUES(10);
INSERT INTO new_dept VALUES(50);
INSERT INTO new_dept VALUES(NULL);

SELECT * FROM new_dept

# 使用 not in 子查询检索存在于dept表而不存在与new_dept表的deptno
SELECT * FROM dept
WHERE deptno  NOT IN (SELECT deptno FROM new_dept)

/*
deptno为20、30和40的数据虽然不在new_dept中，却没有被上述查询检索到。
原因：new_dept中有null值；
in 和 not in 本质上是or运算，由于 null 值参与or运算的方式不同，in 和 not in将会产生不同的结果
*/

#当列表中有null值时使用in
SELECT * FROM dept
WHERE deptno  IN (SELECT deptno FROM new_dept) #结果是10

SELECT * FROM dept
WHERE (deptno=10 OR deptno=50 OR deptno=NULL)  #结果是10

#当列表中有null值时使用not in
SELECT * FROM dept
WHERE deptno  NOT IN (SELECT deptno FROM new_dept) #无结果（结果应该是20,30,40）

SELECT * FROM dept
WHERE NOT (deptno=10 OR deptno=50 OR deptno=NULL)  #无结果（结果应该是20,30,40）


#在sql中：true or null 的结果是true；false or null的结果是null，一旦混入了null，结果会一直保持为null

SELECT * FROM dept d
 WHERE NOT EXISTS (SELECT NULL FROM new_dept nd WHERE nd.deptno=d.deptno)

# exists/not exists 和关联子查询一起使用时，子查询中select列表的项目不重要，重要的是子查询的条件是TRUE/FALSE