我的野蛮王子国语版,麻城新闻,coursehero
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的数据，都会导致最终查询结果为空集。
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
SQL Server免费版的安装以及使用SQL Server Management Studio(SSMS)连接数据库的图文方法
SQL Server 2017 Developer的下载、安装、配置及SSMS的下载安装配置(图文教程详解)
我要评论
网友评论