oracle中关于not in 和 in的坑

首先我们举一个案例

这是部门dept

这是员工emp

 

用in的情况

SELECT
	DEPARTMENT_ID 
FROM
	DEPT
WHERE
	DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMP  )

按照逻辑not in 查询出来的数据应该是4

SELECT
	DEPARTMENT_ID 
FROM
	DEPT
WHERE
	DEPARTMENT_ID not IN ( SELECT DEPARTMENT_ID FROM EMP  )

然而情况与我们想象的不一样

这里in后面有null，能返回数据
但加了not后，就不能返回数据了
这里的in后面的句子可以理解为or拼接，即
id in (1,2,3，null)可以等价于id=1 or id=2 or id=3 or id=null，
id not in (1,2,3，null)可以等价于not(id=1 or id=2 or id=3 or id=null)或id!=1 and id!=2 and id!=3 and id=null。
为什么都是or拼接，in可以而not in不可以呢，可以把not in理解为后面的and表达式就知道了，因为id=null为null，也就相当于false，导致整个表达式为false，无论传何值都为false，自然无法返回数据。

最后对not in 进行优化

SELECT
	DEPARTMENT_ID 
FROM
	DEPT
WHERE
	DEPARTMENT_ID not IN ( SELECT DEPARTMENT_ID FROM EMP where DEPARTMENT_ID is not null )