MySQL中NOT IN接SELECT子查询与值列表输出结果不同原因求解

核心原因：NOT IN与NULL值的逻辑冲突 #

你遇到的差异和别名设置完全无关，是MySQL中NOT IN运算碰到NULL值的经典特性导致的：

• 当执行值 NOT IN (值列表)时，SQL会将运算等价转换为：值 != 列表元素1 AND 值 != 列表元素2 AND ... AND 值 != 列表最后一个元素
• 你的子查询SELECT P FROM bst返回的结果中包含1个NULL值（对应N=15行的P字段），而任何值和NULL做比较的结果都是UNKNOWN（既不是TRUE也不是FALSE）
• 以N=1的判断为例：1 NOT IN (SELECT P FROM bst)最终会被转换为1!=2 AND 1!=2 AND ... AND 1!=15 AND 1!=NULL，最后一个比较的结果是UNKNOWN，整个AND逻辑链的结果就为UNKNOWN，第一个WHEN条件不触发，直接走到ELSE分支判定为Inner，因此所有叶子节点的判断全部失效。

为什么手动列值列表可以正常运行？ #

你手动罗列P列值的时候，漏掉了原表中存在的NULL值，列表里只有非NULL的P值，没有和NULL比较的步骤，逻辑运算就符合预期。

为什么根节点的IN子查询不受影响？ #

IN运算的等价逻辑是值 = 列表元素1 OR 值 = 列表元素2 OR ... OR 值 = 列表最后一个元素：
根节点的N值是15，在P列中存在非NULL的匹配项，OR逻辑链中已经出现了TRUE，后续哪怕碰到15=NULL的UNKNOWN结果，整个表达式的最终结果依然为TRUE，因此根节点的判断不受NULL影响。

修复方案 #

只需要在子查询中过滤掉NULL值即可：

SELECT N,
CASE 
    WHEN P IS NOT Null AND N NOT IN (SELECT P FROM bst WHERE P IS NOT NULL) THEN 'Leaf'
    WHEN P IS Null THEN 'Root'
    ELSE 'Inner'
END
FROM bst
ORDER BY N;

内容的提问来源于stack exchange，提问作者Moe Mori