SQL陷阱-in与not in不是相反的
来源:互联网 发布:微信玩色子作弊软件 编辑:程序博客网 时间:2024/06/15 23:10
SQL中In与Not In的小陷阱
数据库中的三值逻辑
在SQL中,逻辑值与其他编程语言不同,其他编程语言往往只有true
和false
,而在SQL中,还多了一个值UNKNOWN
,当与NULL进行比较时会出现这种值,如(1==NULL)结果为UNKNOWN
。下面看看维基百科的详细说明。
数据库查询语言SQL实现三值逻辑作为处理NULL字段内容的一种方式。SQL使用NULL来表示在数据库中缺失数据。如果一个字段不包含定义的值,对于SQL这意味着实际的值存在,但是这个值当前没有记录在数据库中。注意缺失的值不同于数值零或零长度字符串值;这两者都表示已知的值。比较任何东西于NULL—即使是另一个NULL—结果是UNKNOWN真值状态。例如,考虑下列SQL表达式:City = 'Paris'在SQL中,在City字段中的NULL值表示在理论中导致这个表达式被确认为要么TRUE(比如City包含'Paris')要么FALSE(比如City包含'Philadelphia')的一个缺失的值。样例SQL表达式依据如下规则确认:对于在City字段中有文字串'Paris'的任何记录结果为TRUE对于在City字段中有NULL的任何记录结果为UNKNOWN在所有其他情况结果为FALSE
三值逻辑可能带来的陷阱
正是因为存在着第三值UNKNOWN
,所以容易导致开发者掉入下面的陷阱。
首先假设我们有一个雇员表,雇员有身份证号,姓名,性别,3个属性,其中只有性别可以为NULL,建表语句如下:
CREATE TABLE `emp` ( `id` varchar(18) NOT NULL, `ename` varchar(20) NOT NULL, `esex` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`));
这时候,我们往表里写入一条数据,(445121199001011111,小明,0)
,(445121199001012222,小红,1)
,(445121199002021122,小江,NULL)
。其中0表示性别为男,1表示性别为女。
那么接下来,我们用in进行查询,查询语句如下:
select * from emp where esex in (0,1);
这条语句的意图是想查询性别为男或性别为女的,无性别的将被忽略。很显然,执行语句的结果集数量为2,即查出了小明和小红。
我们来看另外一条语句:
select * from emp where esex not in (0,1);
这条语句原本的意图是想查出性别不是男也不是女的,也就是查出小江的数据。但是结果却是空集。一条数据也没有!
陷阱来源
回顾SQL语法,我们知道执行in或not in时,我们是按照返回的布尔值的真或假,来决定是否将数据加入结果集。那么当判断返回的值为UNKNOWN
时,那么数据必然不会被加入结果集。如果还不明朗,我们可以看看not in的等价关系。
esex not in(0,1)等价于:esex != ANY(0,1),也等价于esex != 0 AND esex != 1
注意这里,esex != 0 AND esex != 1
。当esex为NULL
时,根据上面的内容,我们知道esex!=0
会返回UNKNOWN
,整个表达式的返回值也为UNKNOWN
。再看看具体数据,小江的esex为NULL
,那么小江的这一条记录,返回值为UNKNOWN
,故不会被加到结果集。
常见陷阱
有了上面的铺垫,我们在使用In或Not In时,就应该更加的小心谨慎(其他返回值为布尔类型的也同理)。尤其是子查询,下面是常见的例子
SELECT * FROM emp WHERE emp.`esex` NOT IN (SELECT esex FROM emp)
上面的返回集为空集。注意这里子查询直接使用了emp表,仅仅为了对应上面的结果。在日常开发中,该子查询的emp表可能为任意的关联表,只要该关联表中,存在有esex为NULL的数据,都会导致最终查询结果为空集。
以上均为作者个人总结,如有错误,欢迎友友指正。但愿本文能对您有所帮助:)
- SQL陷阱-in与not in不是相反的
- sql not in 一个与直觉相反的问题
- not in 和 in的陷阱
- T-SQL not in 遇到 null 暗含的陷阱
- not in/not exists 的 null 陷阱
- not in/not exists 的 null 陷阱
- not in/not exists 的 null 陷阱
- not in/not exists 的 null 陷阱
- 【陷阱系列】SQLServer in/not in 陷阱
- SQL中not in的陷阱,有null记录的时候一不小心就踩中了
- sql 在not in 子查询有null值情况下经常出现的陷阱
- sql Exists in 文章1:SQL里的EXISTS与in、not exists与not in 效率比较和使用
- 替代sql的in,not in操作
- sql里的exists与in、not exists与not in的区别
- SQL 中not exist 与 not in
- SQL里的EXISTS与in、not exists与not in
- SQL里的EXISTS与in、not exists与not in
- SQL里的EXISTS与in、not exists与not in
- jquery 报错 e.nodeName.toLowerCase is not a function
- KVM学习笔记
- 【原创译文】基于Docker和Rancher的超融合容器云架构
- 解决mysql"Access denied for user'root'@'IP地址'"问题
- POLYV上传客户端实现批量上传视频功能的介绍
- SQL陷阱-in与not in不是相反的
- 7_20|C语言|随机数的产生|表达式规则
- 线段树详解 (原理,实现与应用)
- Hibernate缓存机制
- easyUI的学习--在线教程,工具选择安装配置过程(一)
- Foxmail不能收取163邮件的解决办法
- 【Away3D代码解读】(五):动画模块及骨骼动画
- python ctypes常用方法
- 并发(九):检查死锁与Locked ownable synchronizers