一文详解SQL 中的三值逻辑

目录
  • 1. 前言
  • 2. 两种 Null
  • 3. 为什么是 is Null 而不是 = Null ?
  • 4. 第三个真值 “unknown”
  • 5. 包含三值逻辑的真值表
  • 6. “排中律” 不再成立
  • 7. CASE 表达式和 NULL
  • 8. NOT IN 和 NOT EXISTS 不是等价的
  • 9. 限定谓词和 NULL
  • 10. 限定谓词和极值函数不是等价的
  • 11. 聚合函数和 Null

1. 前言

大多数编程语言都是基于二值逻辑的,即逻辑真值只有真和假两个。而 SQL 语言则采用一种特别的逻辑体系——三值逻辑,即逻辑真值除了真和假,而 SQL 语言里,除此之外还有第三个值 unknown ,因此这种逻辑体系被称为三值逻辑(three-valued-logic)。

2. 两种 Null

  • 表示未知 (unknown):“不知道戴墨镜的人眼睛是什么颜色”这种情况为例,这个人的眼睛肯定是有颜色的,但是如果他不摘掉眼镜,别人就不知道他的眼睛是什么颜色。这就叫作未知。
  • 表示不适用 (not applicable 或 inapplicable):“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛,所以“眼睛的颜色”这一属性并不适用于冰箱。

“冰箱的眼睛的颜色”这种说法和“圆的体积”“男性的分娩次数”一样,都是没有意义的。平时,我们习惯了说“不知道”,但是“不知道”也分很多种。“不适用”这种情况下的 NULL ,在语义上更接近于“无意义”,而不是“不确定”。

总结:“未知”指的是“虽然现在不知道,但加上某些条件后就可以知道”;而“不适用”指的是“没有办法知道”。

3. 为什么是 is Null 而不是 = Null ?

“我们先从定义一个表示‘虽然丢失了,但却适用的值’的标记开始。我们把它叫作 A-Mark。这个标记在关系数据库里既不被当作值(value),也不被当作变量 (variable)。”(E.F. Codd,The Relational Model for Database Management :Version 2 , P.173) “关于 NULL 的很重要的一件事情是,NULL 并不是值。”(C.J. Date, An Intruction To Database System (6th edition ), P.619)

对 NULL 使用比较谓词后得到的结果总是 unknown 。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行,不会包含判断结果为 false 和 unknown 的行。不只是等号,对 NULL 使用其他比较谓词,结果也都是一样的。

-- 以下的式子都会被判为 unknown
1 = NULL
2 > NULL
3 < NULL
4 <> NULL
NULL = NULL

那么,为什么对 NULL 使用比较谓词后得到的结果永远不可能为真呢?这是因为,NULL 既不是值也不是变量。NULL 只是一个表示“没有值”的标记,而比较谓词只适用于值。因此,对并非值的 NULL 使用比较谓词本来就是没有意义的。(Null只是一个作为区分的标记,并不是一个值)

“列的值为 NULL ”“NULL 值”这样的说法本身就是错误的。因为 NULL 不是值,所以不在定义域(domain)中。相反,如果有人认为 NULL 是值,那么请区分一下:它是什么类型的值?关系数据库中存在的值必然属于某种类型,比如字符型或数值型等。所以,假如 NULL 是值,那么它就必须属于某种类型。( SQL 里的 NULL 和其他编程语言里的 NULL 是完全不同的东西)

4. 第三个真值 “unknown”

因关系数据库采用了 NULL 而被引入了 “第三个真值”。这里有一点需要注意:真值 unknown 和作为 NULL 的一种的 UNKNOWN (未知)是不同的东西。前者是明确的布尔型的真值,后者既不是值也不是变量。(下文使用 unknown 表示 真值,UNKNOWN 表示 代表Null的一个 标记)

举个栗子: unknown = unknown 判定为 true 而 UNKNOWN = UNKNOWN ( 也就是 Null = Null ) 判定为 unknown

5. 包含三值逻辑的真值表

当两个值进行逻辑判断的时候的优先级,优先级高的真值会决定计算结果:

  • AND 的情况: false > unknown > true
  • OR 的情况: true > unknown > false

举个栗子: true AND unknown ,因为 unknown 的优先级更高,所以结果是 unknown 。而 true OR unknown 的话,因为 true 优先级更高,所以结果是 true 。

6. “排中律” 不再成立

“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题” 这个命题在二值逻辑中被称为排中律(Law of Excluded Middle)。顾名思义,排中律就是指不认可中间状态,对命题真伪的判定黑白分明,是古典逻辑学的重要原理。

举个栗子:现实生活中 一个学生 是20岁 或者 不是20岁,不会有第三种情况。

但是在SQL中并不是这个样:

-- 查询年龄是20 岁或者不是20 岁的学生
SELECT *
FROM Students
WHERE age = 20 OR age <> 20;

在现实生活中,上面的查询条件应该包含所有的学生,但是这里的执行结果并不会查询到约翰。

--- 当查询到约翰哪一行时的判定
WHERE Null = 20 OR Null <> 20

--- 根据上文的描述,该条件会转换为
WHERE unknown OR unknown   等同于 WHERE unknown

若要查到所有学生需要再加上一个条件:OR age IS NULL

7. CASE 表达式和 NULL

CASE col_1
    WHEN 1 THEN '○'
    WHEN NULL THEN '×'
END

上面的这个CASE表达式会在 col_1 为 1 时返回  、为 NULL 时返回 × 吗?显然始终不会返回 x 因为第二个 WHEN 子句是 col_1 = NULL 的缩写形式,根据上文的描述 col_1 = NULL 始终会返回 unknown 而 CASE 表达式的判断方法与 WHERE 子句一样,只认可真值为 true 的条件,所以 x 并不会出现。下面才是正确的写法:

CASE
    WHEN col_1 = 1 THEN '○'
    WHEN col_1 IS NULL THEN '×'
END

8. NOT IN 和 NOT EXISTS 不是等价的

在对 SQL 语句进行性能优化时,经常用到的一个技巧是将 IN 改写成EXISTS 。这是等价改写,并没有什么问题。问题在于,将 NOT IN 改写成 NOT EXISTS 时,结果未必一样。

查询 “与 B 班住在东京的学生年龄不同的 A 班学生” 。也就是说,希望查询到的是拉里和伯杰。

-- 查询与 B 班住在东京的学生年龄不同的 A 班学生的 SQL 语句
SELECT *
FROM Class_A
WHERE age NOT IN ( SELECT age FROM Class_B WHERE city = '东京' );

这条 SQL 语句真的能正确地查询到这两名学生吗?遗憾的是不能。结果是空,查询不到任何数据。根据前文所说的规则推导一下吧:

--1. 执行子查询,获取年龄列表
SELECT *
FROM Class_A
WHERE age NOT IN (22, 23, NULL);
--2. 用 NOT 和 IN 等价改写 NOT IN
SELECT *
FROM Class_A
WHERE NOT age IN (22, 23, NULL);

--3. 用 OR 等价改写谓词 IN
SELECT *
FROM Class_A
WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL) );

--4. 使用德 · 摩根定律等价改写
SELECT *
FROM Class_A
WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);

--5. 用<> 等价改写 NOT 和 =
SELECT *
FROM Class_A
WHERE (age <> 22) AND (age <> 23) AND (age <> NULL);

--6. 对NULL 使用<> 后,结果为unknown
SELECT *
FROM Class_A
WHERE (age <> 22) AND (age <> 23) AND unknown;

--7.如果AND 运算里包含unknown,则结果不为true
SELECT *
FROM Class_A
WHERE false 或 unknown;

所以 上述查询语句 查询不到任何数据。为了得到正确的结果,需要使用 EXISTS 谓词。

-- 正确的SQL 语句:拉里和伯杰将被查询到
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = B.age AND B.city = '东京' );

--1. 在子查询里和NULL 进行比较运算
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = NULL AND B.city = '东京' );

--2. 对NULL 使用“=”后,结果为 unknown
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE unknown AND B.city = '东京' );

--3. 如果AND 运算里包含unknown,结果不会是true
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE false 或 unknown);

--4. 子查询没有返回结果,因此相反地,NOT EXISTS 为true
SELECT *
FROM Class_A A
WHERE true;

9. 限定谓词和 NULL

-- 查询比 B 班住在东京的所有学生年龄都小的A 班学生 这里会正常返回 拉里
SELECT *
FROM Class_A
WHERE age < ALL ( SELECT age FROM Class_B WHERE city = '东京' );

如果山田年龄不详,就会有问题了。

--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;

10. 限定谓词和极值函数不是等价的

将 9 中的表 Class_B 中 山田的年龄改为Null,执行下面的查询

-- 查询比B 班住在东京的年龄最小的学生还要小的A 班学生
SELECT *
FROM Class_A
WHERE age < ( SELECT MIN(age) FROM Class_B WHERE city = '东京' );

这里仍能正确查询出拉里和伯杰,这是因为,极值函数在统计时会把为 NULL 的数据排除掉。使用极值函数能使 Class_B 这张表里看起来就像不存在 NULL 一样。

区分含义:

  • ALL 谓词:他的年龄比在东京住的所有学生都小 Q1
  • 极值函数:他的年龄比在东京住的年龄最小的学生还要小 Q2

Q1 和 Q2 不等价的情况:

  • 表里存在 NULL 时它们是不等价的
  • 谓词(或者函数)的输入为空集的情况

这里说明一下情况2:B 班里没有学生住在东京。这时,使用 ALL 谓词的SQL 语句会查询到 A 班的所有学生。然而,用极值函数查询时一行数据都查询不到。这是因为,极值函数在输入为空表(空集)时会返回 NULL 。

--1. 极值函数返回NULL
SELECT *
FROM Class_A
WHERE age < NULL;

--2. 对NULL 使用“<”后结果为 unknown
SELECT *
FROM Class_A
WHERE unknown;

11. 聚合函数和 Null

实际上,当输入为空表时返回 NULL 的不只是极值函数,COUNT 以外的聚合函数也是如此。

-- 查询比住在东京的学生的平均年龄还要小的A 班学生的SQL 语句?
SELECT *
FROM Class_A
WHERE age < ( SELECT AVG(age) FROM Class_B WHERE city = '东京' );

没有住在东京的学生时,AVG 函数返回 NULL 。因此,外侧的 WHERE 子句永远是 unknown ,也就查询不到行。

到此这篇关于一文详解SQL 中的三值逻辑的文章就介绍到这了,更多相关SQL三值逻辑内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 详解mysql三值逻辑与NULL

    什么是NULL NULL 用于表示缺失的值或遗漏的未知数据,不是某种具体类型的值.数据表中的 NULL 值表示该值所处的字段为空,值为 NULL 的字段没有值,尤其要明白的是:NULL 值与 0 或者空字符串是不同的. 两种 NULL 这种说法大家可能会觉得很奇怪,因为 SQL 里只存在一种 NULL .然而在讨论 NULL 时,我们一般都会将它分成两种类型来思考:"未知"(unknown)和"不适用"(not applicable,inapp licable).

  • 一文详解SQL 中的三值逻辑

    目录 1. 前言 2. 两种 Null 3. 为什么是 is Null 而不是 = Null ? 4. 第三个真值 “unknown” 5. 包含三值逻辑的真值表 6. “排中律” 不再成立 7. CASE 表达式和 NULL 8. NOT IN 和 NOT EXISTS 不是等价的 9. 限定谓词和 NULL 10. 限定谓词和极值函数不是等价的 11. 聚合函数和 Null 1. 前言 大多数编程语言都是基于二值逻辑的,即逻辑真值只有真和假两个.而 SQL 语言则采用一种特别的逻辑体系——三

  • 一文详解Java中Stream流的使用

    目录 简介 操作1:创建流 操作2:中间操作 筛选(过滤).去重 映射 排序 消费 操作3:终止操作 匹配.最值.个数 收集 规约 简介 说明 本文用实例介绍stream的使用. JDK8新增了Stream(流操作) 处理集合的数据,可执行查找.过滤和映射数据等操作. 使用Stream API 对集合数据进行操作,就类似于使用 SQL 执行的数据库查询.可以使用 Stream API 来并行执行操作. 简而言之,Stream API 提供了一种高效且易于使用的处理数据的方式. 特点 不是数据结构

  • 一文详解MySQL中数据表的外连接

    目录 为什么要使用外连接 外连接简介 左连接与右连接 外连接练习① 外连接练习② 该章节的内容为多表连接查询的外连接,因为 MySQL 是关系型数据库,数据是拆分重组在多个数据表里面的.所以我们势必要从多个数据表中提取数据,通过 SQL 语句的内连接与外连接就能够实现多表查询了.这部分内容是需要我们重点学习的,学习的过程中会穿插多种的案例来强化对表连接的语法的运用. 为什么要使用外连接 在解释为什么使用 “外连接” 之前,先来看一个记录.(如下:) 针对表中的张三没有所属的部门编号,我们暂且将他

  • 详解SQL中Group By的使用教程

    1.概述 "Group By"从字面意义上理解就是根据"By"指定的规则对数据进行分组,所谓的分组就是将一个"数据集"划分成若干个"小区域",然后针对若干个"小区域"进行数据处理. 2.原始表 3.简单Group By示例1 select 类别, sum(数量) as 数量之和from Agroup by 类别 返回结果如下表,实际上就是分类汇总. 4.Group By 和 Order By示例2 sele

  • 一文详解JS中的事件循环机制

    目录 前言 1.JavaScript是单线程的 2.同步和异步 3.事件循环 前言 我们知道JavaScript 是单线程的编程语言,只能同一时间内做一件事,按顺序来处理事件,但是在遇到异步事件的时候,js线程并没有阻塞,还会继续执行,这又是为什么呢?本文来总结一下js 的事件循环机制. 1.JavaScript是单线程的 JavaScript 是一种单线程的编程语言,只有一个调用栈,决定了它在同一时间只能做一件事.在代码执行的时候,通过将不同函数的执行上下文压入执行栈中来保证代码的有序执行.在

  • 一文详解Java中的类加载机制

    目录 一.前言 二.类加载的时机 2.1 类加载过程 2.2 什么时候类初始化 2.3 被动引用不会初始化 三.类加载的过程 3.1 加载 3.2 验证 3.3 准备 3.4 解析 3.5 初始化 四.父类和子类初始化过程中的执行顺序 五.类加载器 5.1 类与类加载器 5.2 双亲委派模型 5.3 破坏双亲委派模型 六.Java模块化系统 一.前言 Java虚拟机把描述类的数据从Class文件加载到内存,并对数据进行校验.转换解析和初始化,最 终形成可以被虚拟机直接使用的Java类型,这个过程

  • 一文详解C++中运算符的使用

    目录 一.算术运算符 二.关系运算符 三.逻辑运算符 四.位运算符 五.赋值运算符 六.杂项运算符 一.算术运算符 运算符 描述 + 把两个操作数相加 - 从第一个操作数中减去第二个操作数 * 把两个操作数相乘 / 分子除以分母 % 取模运算符,整除后的余数 ++ 自增运算符,整数值增加 1 – 自减运算符,整数值减少 1 通过下面的例子可以让我们更好的理解C++中的运算符的意义与使用方法. #include <iostream> using namespace std; int main()

  • 一文详解Python中生成器的原理与使用

    目录 什么是生成器 迭代器和生成器的区别 创建方式 生成器表达式 基本语法 生成器函数 yield关键字 yield和return yield的使用方法 生成器函数的基本使用 send的使用 可迭代对象的优化 总结 我们学习完推导式之后发现,推导式就是在容器中使用一个for循环而已,为什么没有元组推导式? 原因就是“元组推导式”的名字不是这样的,而是叫做生成器表达式. 什么是生成器 生成器表达式本质上就是一个迭代器,是定义迭代器的一种方式,是允许自定义逻辑的迭代器.生成器使用generator表

  • 一文详解Python中PO模式的设计与实现

    目录 什么是PO模式 PO 三层模式 PO 设计模式的优点 将改写的脚本转为PO设计模式 构建基础的 BasePage 层 构建首页的 Page 层(HomePage) 构建登录页的 Page 层(LoginPage) 构建 首页 - 订单 - 支付 流程的 Page 层(OrderPage) PO 设计模式下测试Case的改造 在使用 Python 进行编码的时候,会使用自身自带的编码设计格式,比如说最常见的单例模式,稍微抽象一些的抽象工厂模式等等… 在利用 Python 做自动化测试的时候,

  • 一文详解C#中方法重载的底层玩法

    目录 一:为什么 C 不支持 二:C++ 符号表突破 三:C#如何实现突破 最近在看 C++ 的方法重载,我就在想 C# 中的重载底层是怎么玩的,很多朋友应该知道 C 是不支持重载的,比如下面的代码就会报错. #include <stdio.h> int say() { return 1; } int say(int i) { return i; } int main() { say(10); return 0; } 从错误信息看,它说 say 方法已经存在了,尴尬... 一:为什么 C 不支

随机推荐