先写一个SQL
|
1
2
3
|
SELECT DISTINCT from_idFROM codWHERE cod.from_id NOT IN (37, 56, 57) |
今天在写SQL的时候，发现这个查的结果不全，少了NULL值的情况，not in 的时候竟然把null也排除了
用 in 的时候却没有包含null
感觉是mysql设计的不合理
因为一直认为in 和 not in 正好应该互补才是，就像这样查的应该是全部的一样：
|
1
2
3
|
SELECT DISTINCT from_idFROM codWHERE cod.from_id NOT IN (37, 56, 57) or cod.from_id IN (37, 56, 57) |
结果正如猜测的那样，少了个null
后来上网上查了下，有一个解释挺合理的，即：
null与任何值比较都是false
比如from_id有(37, 56, 57,28,null)， not in (37, 56, 57)与28比较时是true，所以结果集中出现28，
null与not in (37, 56, 57)这个条件比较时，结果false，所以不出现在结果集中
补充：MySQL条件查询IN和NOT IN左右两侧包含NULL值的处理方式
题目
给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
+----+------+| id | p\_id |+----+------+| 1 | NULL || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 2 |+----+------+ |
树中每个节点属于以下三种类型之一：
叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
+----+------+| id | TYPE |+----+------+| 1 | Root || 2 | INNER|| 3 | Leaf || 4 | Leaf || 5 | Leaf |+----+------+ |
解释
节点 ‘1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 ‘2' 和 ‘3' 。
节点 ‘2' 是内部节点，因为它有父节点 ‘1' ，也有孩子节点 ‘4' 和 ‘5' 。
节点 ‘3', ‘4' 和 ‘5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。
样例中树的形态如下：
|
1
2
3
4
5
6
7
8
9
|
1/ \\2 3/ \\4 5 |
首先先建表
1.建表
|
1
2
3
4
|
CREATE TABLE tree(id INT ,p_id INT) |
下面是我的做法：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT id,(CASE WHEN tree.p_id IS NULL THEN 'Root' WHEN tree.id NOT IN ( -- id不在父结点p_id列时，认为是叶子结点，逻辑上没有问题！ SELECT p_id FROM tree GROUP BY p_id ) THEN 'Leaf' ELSE 'Inner'END)TYPEFROM tree |
我觉得当id不在父结点p_id列时，认为是叶子结点，这在逻辑上完全没有任何问题，然而事情并没有这么简单，查询结果如下：从id=3开始没有查到我想要的结果！神奇吧!
于是又过了一晚上，终于解决了问题，我先给出正确的做法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT id,(CASE WHEN tree.p_id IS NULL THEN 'Root' WHEN tree.id NOT IN ( SELECT p_id FROM tree WHERE p_id IS NOT NULL -- 添加了一句SQL GROUP BY p_id ) THEN 'Leaf' ELSE 'Inner'END)TYPEFROM tree |
为什么会这样呢？
我们都知道
MySQL 中的 IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。
NOT IN 的作用和 IN 恰好相反，NOT IN 用来判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。
一般情况下我们都是这样用的，结果也是我们想要的。但是下面的特殊情况我们却经常遇到！
（1）in和not in左右两侧都没有NULL值的情况
【实例1】在 SQL 语句中使用 IN 和 NOT IN 运算符：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT 2 IN (1,3,5,'thks'),'thks' IN (1,3,5, 'thks');+---------------------+---------------------------+| 2 IN (1,3,5,'thks') | 'thks' IN (1,3,5, 'thks') |+---------------------+---------------------------+| 0 | 1 |+---------------------+---------------------------+1 row in set, 2 warnings (0.00 sec)mysql> SELECT 2 NOT IN (1,3,5,'thks'),'thks' NOT IN (1,3,5, 'thks');+-------------------------+-------------------------------+| 2 NOT IN (1,3,5,'thks') | 'thks' NOT IN (1,3,5, 'thks') |+-------------------------+-------------------------------+| 1 | 0 |+-------------------------+-------------------------------+1 row in set, 2 warnings (0.00 sec) |
由结果可以看到，IN 和 NOT IN 的返回值正好相反。
但是忽略了一个NULL值问题
对空值 NULL 的处理
当 IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 1。
（2）NULL值在in左右两侧
请看下面的 SQL 语句如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');+------------------------+-------------------------+| NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |+------------------------+-------------------------+| NULL | NULL |+------------------------+-------------------------+1 row in set, 1 warning (0.00 sec)mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');+------------------------+--------------------------+| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |+------------------------+--------------------------+| NULL | 1 |+------------------------+--------------------------+1 row in set (0.00 sec) |
（3）NULL在NOT IN 的其中一侧
NOT IN 恰好相反，当 NOT IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 0。
请看下面的 SQL 语句如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,0,NULL,'thks');+----------------------------+-----------------------------+| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,0,NULL,'thks') |+----------------------------+-----------------------------+| NULL | NULL |+----------------------------+-----------------------------+1 row in set, 1 warning (0.00 sec)mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,10,NULL,'thks');+----------------------------+------------------------------+| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,10,NULL,'thks') |+----------------------------+------------------------------+| NULL | 0 |+----------------------------+------------------------------+1 row in set (0.00 sec) |
根据（3）NULL在NOT IN 的其中一侧的结果，这就可以看出问题
先来查询下面SQL语句，慢慢发现问题
|
1
2
3
|
SELECT p_idFROM treeGROUP BY p_id |
上面查询结果包含了NULL值
所以查询下面SQL语句就查不到任何东西，这是因为NOT IN返回了NULL
|
1
2
3
4
5
6
7
|
SELECT id FROM treeWHERE id NOT IN ( SELECT p_id FROM tree GROUP BY p_id ) |
所以要想查询出来结果就要先把NULL值给处理掉！好了，Bug搞定！
这题还有另外一种做法：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT id,(CASE WHEN tree.p_id IS NULL THEN 'Root' WHEN tree.id IN ( SELECT p_id FROM tree GROUP BY p_id ) THEN 'Inner' ELSE 'Leaf'END)TYPEFROM tree |
为什么是对的？留给大家想想吧~
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/weixin_35711816/article/details/80634795
