先写一个sql
select distinct from_id
from cod
where cod.from_id not in (37, 56, 57)
今天在写sql的时候,发现这个查的结果不全,少了null值的情况,not in 的时候竟然把null也排除了
用 in 的时候却没有包含null
感觉是mysql设计的不合理
因为一直认为in 和 not in 正好应该互补才是,就像这样查的应该是全部的一样:
select distinct from_id
from cod
where 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 。
+----+------+
| 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 |
+----+------+
解释
节点 ‘1' 是根节点,因为它的父节点是 null ,同时它有孩子节点 ‘2' 和 ‘3' 。
节点 ‘2' 是内部节点,因为它有父节点 ‘1' ,也有孩子节点 ‘4' 和 ‘5' 。
节点 ‘3', ‘4' 和 ‘5' 都是叶子节点,因为它们都有父节点同时没有孩子节点。
样例中树的形态如下:
1
/ \\
2 3
/ \\
4 5
首先先建表
1.建表
create table tree(
id int ,
p_id int
)
下面是我的做法:
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
)type
from tree
我觉得当id不在父结点p_id列时,认为是叶子结点,这在逻辑上完全没有任何问题,然而事情并没有这么简单,查询结果如下:从id=3开始没有查到我想要的结果!神奇吧!
于是又过了一晚上,终于解决了问题,我先给出正确的做法:
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
)type
from tree
为什么会这样呢?
我们都知道
mysql 中的 in 运算符用来判断表达式的值是否位于给出的列表中;如果是,返回值为 1,否则返回值为 0。
not in 的作用和 in 恰好相反,not in 用来判断表达式的值是否不存在于给出的列表中;如果不是,返回值为 1,否则返回值为 0。
一般情况下我们都是这样用的,结果也是我们想要的。但是下面的特殊情况我们却经常遇到!
(1)in和not in左右两侧都没有null值的情况
【实例1】在 sql 语句中使用 in 和 not in 运算符:
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 语句如下:
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 语句如下:
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语句,慢慢发现问题
select p_id
from tree
group by p_id
上面查询结果包含了null值
所以查询下面sql语句就查不到任何东西,这是因为not in返回了null
select id
from tree
where id not in (
select p_id
from tree
group by p_id
)
所以要想查询出来结果就要先把null值给处理掉!好了,bug搞定!
这题还有另外一种做法:
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
)type
from tree
为什么是对的?留给大家想想吧~
以上为个人经验,希望能给大家一个参考,也希望大家多多支持萬仟网。如有错误或未考虑完全的地方,望不吝赐教。
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!