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
)

下面是我的做法:

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

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

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

(0)

相关推荐

  • MySQL中对于not in和minus使用的优化

    优化前: select count(t.id) from test t where t.status = 1 and t.id not in (select distinct a.app_id from test2 a where a.type = 1 and a.rule_id in (152, 153, 154)) 17:20:57 laojiu>@plan PLAN_TABLE_OUTPUT ----------------------------------------- Plan ha

  • MySql如何使用not in实现优化

    最近项目上用select查询时使用到了not in来排除用不到的主键id一开始使用的sql如下: select s.SORT_ID, s.SORT_NAME, s.SORT_STATUS, s.SORT_LOGO_URL, s.SORT_LOGO_URL_LIGHT from SYS_SORT_PROMOTE s WHERE s.SORT_NAME = '必听经典' AND s.SORT_ID NOT IN ("SORTID001") limit 1; 表中的数据较多时这个sql的执行

  • MySQL exists 和in 详解及区别

    MySQL exists 和in 详解及区别 有一个查询如下: SELECT c.CustomerId, CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID = cu.CustomerID) 这里面的EXISTS是如何运作呢?子查询返回的是OrderId字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊

  • 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 c

  • MySQL查询条件常见用法详解

    本文实例讲述了MySQL查询条件常见用法.分享给大家供大家参考,具体如下: 条件 使用where子句对表中的数据筛选,结果为true的行会出现在结果集中 语法如下: select * from 表名 where 条件; 例: select * from students where id=1; where后面支持多种运算符,进行条件的处理 比较运算符 逻辑运算符 模糊查询 范围查询 空判断 比较运算符 等于: = 大于: > 大于等于: >= 小于: < 小于等于: <= 不等于:

  • MySQL查询条件中in会用到索引吗

    当用人问你MySQL 查询条件中 in 会不会用到索引,你该怎么回答? 答案:可能会用到索引 动手来测试下 1.创建一张表,给字段port建立索引 CREATE TABLE `pre_request_logs_20180524` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ip` char(16) NOT NULL COMMENT '代理IP', `port` int(8) NOT NULL COMMENT '端口号', `status` enum('成功'

  • MySQL查询条件中放置on和where的区别分析

    导语 今天在写 SQL 的时候,遇到一个问题.需求是这样的,查询数据,按照评分倒序.近一周访问量倒序,这样进行排序.问题是常规的写法,将 day >= xxx 条件放到 where 中, 如果某些数据近一周没有访问量,那么这条数据就查不出来.解决办法呢,就是将条件放到 LEFT JOIN 中. MySQL 语句执行顺序 首先先说明一个概念,MySQL 语句执行的顺序,并不是按照 SQL 语句的顺序.下面是示例 SQL SELECT DISTINCT < select_list > FRO

  • 详解Mysql查询条件中字符串尾部有空格也能匹配上的问题

    一.表结构 TABLE person id name 1 你 2 你(一个空格) 3 你(二个空格) 二.查询与结果 select * from person where `name` = ? 无论 ? = "你 + 几个空格",都会检索出全部三个结果. 三.原因 MySQL 校对规则属于PADSPACE,会忽略尾部空格 针对的是 varchar char text -- 等文本类的数据类型 此为 SQL 标准化行为.无需要设置也无法改变. 四.想要精确查询怎么办? 方法一:like

  • MySQL详解如何优化查询条件

    目录 前言 现状 问题一 多表联查 单表查询 结论 问题二 多表联查 单表查询 问题 如何解决 前言 技术能解决的事情改技术 技术解决不了的事情该需求 现状 假设我们目前有两张表 业务表 书( t_a_book ) 阅读历史记录表 (t_r_book_history) 用户表 其两张表的数据逻辑如下 t_a_book t_r_book_history t_a_user 当然了,我们假设当前的数据量并不只是我们眼前看到的这几条数据,而是线上真实情况. 每张表至少都是10w+起步 问题一 这时候,我

  • Mysql查询以某"字符串"开头的查询方式

    目录 Mysql查询以某"字符串"开头的查询 查询不以某个或者某些字符串为开头的字符串 查询以某个或者某些字符串为开头的字符串 Mysql查询条件字符串类型 = 0 假如有表A A表中有以下数据 Mysql查询以某"字符串"开头的查询 查询不以某个或者某些字符串为开头的字符串 1.使用left()函数 select * from order where left(id,2)<>"AB"; 2.使用like select * from

  • 关于Mysql查询带单引号及插入带单引号字符串问题

    Mysql查询带引号和不带引号区别 当数据库字段ID为整型时 select ID from table where ID=1 和 select ID from table where ID='1' 两条sql都是可以的,但是第一条sql不用进行隐式转换,速度上比第二条sql略快一些 今天在向mysql数据库中插入带单引号字符串的时候,什么错也没报就是语句执行失败,后来才知道,单引号等要转义,可以使用函数:mysql_real_escape_string和addslashes函数: 以下做个介绍:

  • mysql查询时offset过大影响性能的原因和优化详解

    前言 mysql查询使用select命令,配合limit,offset参数可以读取指定范围的记录.本文将介绍mysql查询时,offset过大影响性能的原因及优化方法. 准备测试数据表及数据 1.创建表 CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NU

  • mysql根据json字段内容作为查询条件(包括json数组)检索数据

    最近用到了mysql5.7的json字段的检索查询,发现挺好用的,记录一下笔记我们有一个日志表,里面的data字段是保存不同对象的json数据,遇到想根据里面的json的字段内容作为条件查询的情况 mysql根据json字段的内容检索查询数据 使用 字段->'$.json属性'进行查询条件 使用json_extract函数查询,json_extract(字段,"$.json属性") 根据json数组查询,用JSON_CONTAINS(字段,JSON_OBJECT('json属性'

随机推荐