SQL陷阱-in与not in不是相反的
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`)
);123456
这时候,我们往表里写入一条数据,(445121199001011111,小明,0)
,(445121199001012222,小红,1),(445121199002021122,小江,NULL)。其中0表示性别为男,1表示性别为女。
那么接下来,我们用in进行查询,查询语句如下:
select * from emp where esex in (0,1);1
这条语句的意图是想查询性别为男或性别为女的,无性别的将被忽略。很显然,执行语句的结果集数量为2,即查出了小明和小红。
我们来看另外一条语句:
select * from emp where esex not in (0,1);1
这条语句原本的意图是想查出性别不是男也不是女的,也就是查出小江的数据。但是结果却是空集。一条数据也没有!
陷阱来源
回顾SQL语法,我们知道执行in或not in时,我们是按照返回的布尔值的真或假,来决定是否将数据加入结果集。那么当判断返回的值为UNKNOWN时,那么数据必然不会被加入结果集。如果还不明朗,我们可以看看not in的等价关系。
esex not in(0,1)等价于:
esex != ANY(0,1),也等价于
esex != 0 AND esex != 1123
注意这里,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)1
上面的返回集为空集。注意这里子查询直接使用了emp表,仅仅为了对应上面的结果。在日常开发中,该子查询的emp表可能为任意的关联表,只要该关联表中,存在有esex为NULL的数据,都会导致最终查询结果为空集。
推荐阅读
-
冷门知识 西葫芦是不是角瓜?角瓜与西葫芦的区别?
-
php中$_GET与$_POST过滤sql注入的方法
-
Oracle与MySQL的分页查询sql语句格式实例讲解
-
SQL Server中与IO相关的等待类型:IO_COMPLETION和PAGEIOLATCH_*
-
SQL SERVER的优化建议与方法
-
在SQL Server中查询资料库的TABLE数量与名称的sql语句
-
SQL Server 压缩日志与减少SQL Server 文件大小的方法
-
SQL Server中的SQL语句优化与效率问题
-
Oracle性能优化——SQL基线(SQLbaseline)的载入与进化(11g中引入的基线)
-
浅谈一次与sql注入 & webshell 的美丽“邂逅”