SQL总复习三:true、false、unknown 和 null
什么是谓词?
SQL 的保留字中,有很多都被归为谓词一类。例如,“=、<、>”等比较谓词,以及 BETWEEN、LIKE、IN、IS NULL 等。
谓词是一种特殊的函数,返回值是真值。前面提到的每个谓词,返回值都是 true、false 或者 unknown(一般的谓词逻辑里没有unknown,但是 SQL 采用的是三值逻辑,因此具有三种真值)。
1、SQL中的bool类型的值有三种
普通编程语言里的布尔型只有 true
和 false
两个值,这种逻辑体系被称为二值逻辑。而 SQL 语言里,除此之外还有第三个值 unknown
,因此这种逻辑体系被称为三值逻辑(three-valued logic)。
三个真值之间有下面这样的优先级顺序。
AND 的情况:false > unknown > true
OR 的情况:true > unknown > false
优先级高的真值会决定计算结果。例如 true AND unknown,因为unknown的优先级更高,所以结果是 unknown。而true OR unknown的话,因为 true 优先级更高,所以结果是 true。
unknown
是因关系数据库采用了 NULL 而被引入的,他不是“未知”的这个意思,而是“无意义”的这个意思。而null是指“未知”的意思。注意:unknown不能像true或者false一样,直接在SQL中使用,比如
where Tel=unknown
。
2、null不是值,null与数学运算符一起使用的结果永远是unknown
为什么对 NULL 使用比较谓词后得到的结果永远不可能为真呢?这是因为,NULL 既不是值也不是变量。NULL 只是一个表示“没有值”的标记,而比较谓词只适用于值。因此,对并非值的 NULL 使用比较谓词本来就是没有意义的。
常听到的“列的值为 NULL
” 、“NULL
值”这样的说法本身就是错误的。因为 NULL
不是值!(如果有人认为 NULL
是值,那么它是什么类型的值?关系数据库中存在的值必然属于某种类型,比如字符型或数值型等。所以,假如 NULL
是值,那么它就必须属于某种类型。)
消除 NULL 的具体方法,这里总结如下。
(1) 首先分析能不能设置默认值。
(2) 仅在无论如何都无法设置默认值时允许使用 NULL。
笔者认为,如果遵守这两条原则,那就足以避免 NULL 带来的各种问题,使系统开发能够更加顺利地进行。
另外,注意:要想 和 null 比较 只能用 is null
或者 is not null
,这样才会返回true或者false。另外永远记住一点,null和<,>,=,<>这些放在一起结果永远是unknown,比如如 2=null,结果肯定是unknown,而unknown在三值逻辑中不是true也不是false,在写where子句的筛选条件时尤其要注意。举例来讲:
我们经常会遇到判断筛选条件的结果(为true/false/unknown的一种,且SQL只会取返回结果是true的记录),它们通常是and 或or连接这些单个条件的,如:where age>18 and sex=0或where age<18 or sex =unknown。
请务必牢记:
and运算,只要有一边是unknown,另一边是false,那结果就是false,其它情况下,只要任意一边有unknown,结果就是unknown。
or运算,只要一边是unknown,那么结果永远就是unknown
not unknown 的结果是 unknown
case与null
当case使用的变量或列的值可能为null时,唯一正确的使用方式如下:
CASE WHEN col_1 = 1 THEN '○' WHEN col_1 IS NULL THEN '×' END
而不是:
CASE col_1 WHEN 1 THEN '○' WHEN NULL THEN '×' END
3、NOT IN 和 NOT EXISTS 不是等价的
如果 NOT IN
子查询中用到的表里被选择的列中存在 NULL
,则 SQL 语句整体的查询结果永远是空。
EXISTS
谓词永远不会返回 unknown
。EXISTS
只会返回 true
或者 false
。
因此就有了 IN
和 EXISTS
可以互相替换使用,而 NOT IN
和 NOT EXISTS
却不可以互相替换的混乱现象。
4、ALL
运算符与null
以下是ALL
运算符语法:
scalar_expression comparison_operator ALL ( subquery )
在上面语法中,
scalar_expression
是任何有效的表达式。comparison_operator
是任何有效的比较运算符,包括等于(=
),不等于(<>
),大于(>
),大于或等于(>=
),小于(<
),小于或等于(<=
)。- 括号内的子查询(
subquery
)是一个SELECT语句,它返回单个列的结果。 此外,返回列的数据类型必须与标量表达式的数据类型相同。
如果all里面的子查询返回的单列中有null的存在,那么这个all表达式就永远不会筛选出任何数据,结果肯定为空。
因为ALL
谓词其实是多个以 AND
连接的逻辑表达式的省略写法。
如果all里面的子查询返回的单列中有null的存在,比如子查询结果如下面这个情况,那么具体的分析步骤如下所示。
--1. 执行子查询获取年龄列表 SELECT * FROM Class_A WHERE age < ALL ( 22, 23, NULL ); --2. 将ALL 谓词等价改写为AND SELECT * FROM Class_A WHERE (age < 22) AND (age < 23) AND (age < NULL); --3. 对NULL 使用“<”后,结果变为 unknown SELECT * FROM Class_A WHERE (age < 22) AND (age < 23) AND unknown; --4. 如果AND 运算里包含unknown,则结果不为true SELECT * FROM Class_A WHERE false 或 unknown; --5.查询结果为空
5、极值函数(max, min)、count以外的聚合函数(sum,average)与null
极值函数在输入为空表(空集)时会返回 NULL。即从一个为空的集合中,选取最大或最小值,会得到null。
因此,建议在使用这些可能返回null的函数的地方,外面套一层isnull
函数(SqlServer)来处理。
count与null
COUNT 函数的使用方法有 COUNT(*) 和 COUNT( 列名 ) 两种,它们的区别有两个:第一个是性能上的区别;第二个是 COUNT(*) 可以用于 NULL, 而 COUNT( 列名 )与其他聚合函数一样,要先排除掉 NULL 的行再进行统计。
第二个区别也可以这么理解:COUNT(*) 查询的是所有行的数目,而COUNT( 列名 ) 查询的则不一定是。
参考图灵社区的《SQL进价教程》
更新于:2023-4-6
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤