Mysql的not in和null都存在时的坑

Mysql的not in 的坑

懒得看题目可以直接跳到个人测试见结论！

题目：leetCode 608

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

±—±-----+
| id | p_id |
±—±-----+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
±—±-----+
树中每个节点属于以下三种类型之一：

叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

±—±-----+
| id | Type |
±—±-----+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
±—±-----+

代码：

优先判断好判断的Root和叶子节点，剩下的都是inner

select
    id,
    case
    when p_id is null then "Root"
    when id not in (select p_id from tree) then "Leaf"
    else "Inner"
    end as Type
from
    tree
1
2
3
4
5
6
7
8
9

• 但是上述代码永远不会有Leaf产生！！！！！
• 原因就是 when id not in (select p_id from tree) then “Leaf”
• 中的id not in (select p_id from tree) 每次都是false

原因介绍：(来自leetCode某个大佬)

A not in B的原理是拿A表值与B表值做是否不等的比较, 也就是a != b. 在sql中, null是缺失未知值而不是空值(详情请见MySQL reference).

当你判断任意值a != null时, 官方说, “You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL”, 任何与null值的对比都将返回null. 因此返回结果为否,这点可以用代码 select if(1 = null, 'true', 'false')证实.

从上述原理可见, 当询问 id not in (select p_id from tree)时, 因为p_id有null值, 返回结果全为false, 于是跳到else的结果, 返回值为inner. 所以在答案中,leaf结果从未彰显,全被inner取代.

个人测试：任何与Null值的对比都将返回Null，理解为false。

#判断in的方法就是，拿前面的与后面的进行匹配，能匹配上一个就返回true，所以Null对In没有影响
select if(('1') in ('1','2','3'),'true','false') #true
select if(('1')  in ('1','2',null),'true','false') #true
select if(('1')  in (null,'1','2'),'true','false') #true

#判断Not in的方法，拿前面的与所有的后面进行匹配，必须每个都为true才是true
select if(('1') not in ('1','2','3'),'true','false') #false
select if(('4') not in ('1','2','3'),'true','false') #true

#但是这里后面加上null的时候，拿4与Null进行not in的对比的时候（不管是不是Not in，其他方法都是一样），直接返回false
select if(('4') not in ('1','2','3',null),'true','false') #false

#进一步测试与null进行对比的返回值
select IF(1=null,'true','false') #false
select IF(1!=null,'true','false') #false
select IF(null=null,'true','false') #false
select IF(null=null,'true','false')  #false
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17