NULL值
Sql的NULL值表示缺失的值。它使用的是三值谓词逻辑。
三值谓词逻辑
平时我们都是习惯使用二值逻辑(TRUE,FALSE)来进行思考,二值逻辑的计算结果不是TRUE就是FALSE。但是SQL中涉及到了NULL值,那么TRUE,FALSE就不够用了,这个时候二值逻辑已经不能支持NULL的出现。所以三值谓词逻辑就登场了。
定义
TRUE,FALSE,NULL(UNKNOWN),如果逻辑表达式只涉及已经存在的值时,那么最终的计算结果要么为TRUE,要么为FALSE,但是当逻辑表达式涉及缺少的值时,其计算结果就是UNKNOWN。
实际存在的值和NULL做比较,得到的结果是UNKNOWN。
*接受TRUE,拒绝FALSE和UNKNOWN。
*拒绝FALSE,接受TRUE和UNKNOWN。
看个示例:
select * from Test where age>12
这里只接受age大于12的列,拒绝那些age小于12或者age等于NULL的列。
当age=NULL的时候,NULL>12计算的结果是UNKNOWN。
age<12或者=12都是同理。
补充:
UNKNOWN取反仍然是UNKNOWN
5>10=TRUE,5<10=FALSE
5>NULL OR 5<NULL OR 5=NULL 都是UNKNOWN
表达式 | 接受的列 | 拒绝的列 |
Age>10 | Age>10=TRUE的列 | Age<=10和Age=NULL的列 |
Age<10 | Age<10=TRUE的列 | Age>=10和Age=NULL的列 |
Age=10 OR >= <= <> 原理同上,接受TRUE,拒绝FALSE和UNKNOWN。 |
特别介绍 EXISTS和IN
===EXISTS和IN之间的区别===
1.EXISTS只返回TRUE或FALSE,不会返回UNKNOWN。
2.IN当遇到包含NULL的情况,那么就会返回UNKNOWN。
重点:
当查询的列包含NULL时,NOT EXISTS正常返回TRUE或FALSE。
而NOT IN可能返回空集,如下
1:val IN(val1,val2,...,NULL),永远不会返回FALSE,而是返回TRUE或UNKNOWN。
2:val NOT IN(val1,val2,...,NULL),永远不会返回TRUE,而是返回NOT TRUE或NOT UNKNOWN。
看个示例:
Test表
Test1表
select t.[name] from Test as t
where exists (select t1.orderid from Test1 as t1 where t1.[name]=t.[name])
返回 aaa,ccc,ddd
select t.[name] from Test as t
where t.[name] in (select t1.[] from Test1 as t1)
返回 aaa,ccc,ddd
select t.[name] from Test as t
where not exists (select t1.orderid from Test1 as t1 where t1.[name]=t.[name])
返回 bbb
select t.[name] from Test as t
where t.[name] not in (select t1.[name] from Test1 as t1)
返回空集
运算符 | 返回值 |
a IN (a,b) | TRUE |
a IN (b,c) | FALSE |
a NOT IN (a,b) | FALSE |
a NOT IN (b,c) | TRUE |
a IN (a,b,NULL) | TRUE |
a IN (b,c,NULL) | UNKNOWN |
a NOT IN (a,b,NULL) | UNKNOWN |
a NOT IN (b,c,NULL) | UNKNOWN |
a EXISTS(a,b) | TRUE |
a EXISTS(b,c) | FALSE |
a NOT EXISTS(a,b) | FALSE |
a NOT EXISTS(b,c) | TRUE |
a EXISTS(a,b,NULL) | TRUE |
a EXISTS(b,c,NULL) | FALSE |
a NOT EXISTS(a,b,NULL) | FALSE |
a NOT EXISTS(b,c,NULL) | TRUE |