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