MySQL查询第一列未出现在第二列的值时NOT IN返回空集如何解决

错误原因 #

你的SQL返回空集的核心问题是NOT IN 子查询的结果集中包含NULL值，触发了SQL的三值逻辑规则，导致所有行的查询条件都不成立。

原理详解 #

1. 先看子查询的返回结果
   你执行的子查询select distinct p from bst会返回p列的所有去重值，而你的bst表中p列存在一个NULL值，因此子查询的结果里包含NULL。

2. SQL三值逻辑规则
   SQL的逻辑运算有三种返回结果：TRUE、FALSE、UNKNOWN。任何和NULL的比较运算，结果都是UNKNOWN：

• 比如1 = NULL结果是UNKNOWN
• 1 != NULL结果也是UNKNOWN
  只有条件结果为TRUE的行才会被WHERE子句选中。

3. NOT IN的运算逻辑
   n NOT IN (值1, 值2, ..., NULL)等价于把所有值用AND连接做不等判断，也就是：
   n != 值1 AND n != 值2 AND ... AND n != NULL
   因为其中n != NULL的结果固定是UNKNOWN，而AND运算中只要有一个条件结果为UNKNOWN，整体运算结果就是UNKNOWN，永远不会等于TRUE，因此所有行都被过滤，最终返回空集。

解决方法 #

方案1：过滤子查询中的NULL值 #

直接在子查询里排除NULL即可正常返回结果：

select n from bst where n not in(select distinct p from bst where p is not null);

方案2：用NOT EXISTS替代NOT IN #

NOT EXISTS不会触发三值逻辑的NULL问题，更推荐这类场景使用：

select n from bst a
where not exists (select 1 from bst b where b.p = a.n);

内容的提问来源于stack exchange，提问作者Midhun Raj