mysql查询条件not in 和 in的区别及原因说明
先写一个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 )
狼蚁网站SEO优化是我的做法
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。
一般情况下我们都是这样用的,结果也是我们想要的。狼蚁网站SEO优化的特殊情况我们却经常遇到!
(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左右两侧
请看狼蚁网站SEO优化的 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。
请看狼蚁网站SEO优化的 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 的其中一侧的结果,这就可以看出问题
先来查询狼蚁网站SEO优化SQL语句,慢慢发现问题
SELECT p_id FROM tree GROUP BY p_id
上面查询结果包含了NULL值
所以查询狼蚁网站SEO优化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
为什么是对的?留给大家想想吧~
以上为个人经验,希望能给大家一个参考,也希望大家多多支持狼蚁SEO。如有错误或未考虑完全的地方,望不吝赐教。
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南