欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL陷阱-in与not in不是相反的

程序员文章站 2022-05-31 08:18:00
SQL中In与Not In的小陷阱 数据库中的三值逻辑 在SQL中,逻辑值与其他编程语言不同,其他编程语言往往只有true和false,而在SQL中,还多了一个值UNKNOWN...

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的数据,都会导致最终查询结果为空集。