DDR爱好者之家

mysql查询条件not in 和 in的区别及原因说明
站长资源 2024/11/24 佚名
40 1538
DDR爱好者之家 Design By 杰米

先写一个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开始没有查到我想要的结果！神奇吧!

mysql查询条件not in 和 in的区别及原因说明

于是又过了一晚上，终于解决了问题，我先给出正确的做法：

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查询条件not in 和 in的区别及原因说明

为什么会这样呢？

我们都知道

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值

mysql查询条件not in 和 in的区别及原因说明

所以查询下面SQL语句就查不到任何东西，这是因为NOT IN返回了NULL

SELECT id 
FROM tree
WHERE id NOT IN (
 SELECT p_id
 FROM tree
 GROUP BY p_id
 )

mysql查询条件not in 和 in的区别及原因说明

所以要想查询出来结果就要先把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

mysql查询条件not in 和 in的区别及原因说明

为什么是对的？留给大家想想吧~

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

DDR爱好者之家 Design By 杰米
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
DDR爱好者之家 Design By 杰米
ddrfans.com DDR爱好者之家
8,675无损音乐
1,324高清电影
213破解软件
70,141收录资讯

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网
DDR爱好者之家 Design By 杰米