当前位置：首页 > article >正文

【笔记】SQL进阶教程（第二版）

article 2025/3/19 2:18:51

备注：以下SQL语法只能运行在SQL Server中。

Case表达式

CASE表达式是不依赖于具体数据库的技术，具有提高SQL代码的可移植性等优点。

CASE表示有两种语法，简单和搜索表达式

-- 简单CASE表达式

DECLARE @SEX VARCHAR = '0'

SELECT

CASE @SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END;

-- 搜索CASE表达式

SELECT

CASE

WHEN @SEX = '1' THEN '男'

WHEN @SEX = '2' THEN '女'

ELSE '其他' END;

也可以将等于符号改为in，这样就可以包括多个条件了。

注意事项：

1、返回的条件类型要一致

2、不要忘记写End

3、形成写Else的习惯，便于快读定位错误位置

非定制化统计

将列进行分组后，得出各个组的总数

-- 对各个地区进行人口统计

SELECT CASE Pref_name

WHEN '德岛' then '四国'

WHEN '香川' then '四国'

WHEN '爱媛' then '四国'

WHEN '高知' then '四国'

WHEN '福冈' then '九州'

WHEN '佐贺' then '九州'

WHEN '长崎' then '九州'

else '其他' End AS '地区',

sum(population)

FROM PopTbl

GROUP BY CASE PREF_NAME

WHEN '德岛' then '四国'

WHEN '香川' then '四国'

WHEN '爱媛' then '四国'

WHEN '高知' then '四国'

WHEN '福冈' then '九州'

WHEN '佐贺' then '九州'

WHEN '长崎' then '九州'

else '其他' End;

必须在SELECT子句和GROUP BY子句这两处写一样的CASE表达式，这有点麻烦。后期需要修改的时候，很容易发生只改了这一处而忘掉改另一处的失误。

行转列

多条件统计是CASE表达式的著名用法之一。它也成为行转列。

-- 多条件查询

select pref_name, sum(case when sex = 1 then population else 0 end) as '男',

sum(case when sex = 2 then population else 0 end) as '女'

from PopTbl2

group by pref_name

这个技巧可贵的地方在于，它能将SQL的查询结果转换为二维表的格式。如果只是简单地用GROUP BY进行聚合，那么查询后必须通过宿主语言或者Excel等应用程序将结果的格式转换一下，才能使之成为交叉表。看上面的执行结果会发现，此时输出的已经是侧栏为县名、表头为性别的交叉表了。在制作统计表时，这个功能非常方便。如果用一句话来形容这个技巧，可以这样说：

新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支。

在UPDATE语句里进行条件分支

假设现在需要根据以下条件对该表的数据进行更新。

01.对当前工资为30万日元以上的员工，降薪10%。

02.对当前工资为25万日元以上且不满28万日元的员工，加薪20%。

按照这些要求更新完的数据应该如下表所示：

乍一看，分别执行下面两个UPDATE操作好像就可以做到，但这样做的结果是不正确的。

-- 条件 1

UPDATE Salaries

SET salary = salary * 0.9

WHERE salary >= 300000;

-- 条件 2

UPDATE Salaries

SET salary = salary * 1.2

WHERE salary >= 250000 AND salary < 280000;

我们来分析一下不正确的原因。例如这里有一个员工，当前工资是30万日元，按“条件1”执行UPDATE操作后，工资会被更新为27万日元，但继续按“条件2”执行UPDATE操作后，工资又会被更新为32.4万日元。这样一来，本来应该被降薪的员工却被加薪了2.4万日元。

这样的结果当然并非人事部所愿。员工相田的工资必须被准确地降为27万日元。问题在于，第1次的UPDATE操作执行后，“当前工资”发生了变化，如果还拿它当作第2次UPDATE的判定条件，结果就会不准确。然而，即使将两条SQL语句的执行顺序颠倒一下，当前工资为27万日元的员工，其工资的更新结果也会出现问题。为了避免出现这些问题，准确地表达出可恶的人事部长的意图，可以像下面这样用CASE表达式来写SQL。

UPDATE Personnel

SET salary = CASE WHEN salary >= 300000

THEN salary * 0.9

WHEN salary >= 250000 AND salary < 280000

THEN salary * 1.2

ELSE salary END;

这条SQL语句不仅执行结果正确，而且因为只需执行1次，所以性能也更高。

需要注意的是，SQL语句最后一行的ELSE salary非常重要，必须写上。因为如果没有它，条件1和条件2都不满足的员工的工资就会被更新成NULL。这一点与CASE表达式的设计有关，在前面介绍CASE表达式的时候我们就已经了解到，如果CASE表达式里没有明确指定ELSE子句，执行结果会被默认地处理成ELSE NULL。现在大家明白笔者最开始强调使用CASE表达式时要习惯性地写上ELSE子句的理由了吧？

表之间的数据匹配

与DECODE函数等相比，CASE表达式的一大优势在于能够判断表达式。也就是说，在CASE表达式里，我们能使用BETWEEN、LIKE和 <、>等便利的谓词组合，还能嵌套子查询的IN和EXISTS谓词。因此，CASE表达式具有非常强大的表达能力。

如下所示，这里有一张培训学校的课程一览表和一张展示每个月所设课程的表。

我们要用这两张表来生成下面这样的交叉表，以便于一目了然地知道每个月开设的课程。

执行结果：

course_name 6 月 7 月 8 月

----------- ---- ---- ----

会计入门〇 × ×

财务知识 × × 〇

簿记考试〇 × ×

税务师〇〇〇

我们需要做的是，检查表OpenCourses中的各月里有表CourseMaster中的哪些课程。这个匹配条件可以用CASE表达式来写。

-- 表的匹配：使用 IN 谓词

SELECT course_name,

CASE WHEN course_id IN

(SELECT course_id FROM OpenCourses

WHERE month = 201806) THEN '〇'

ELSE '×' END AS "6 月",

CASE WHEN course_id IN

(SELECT course_id FROM OpenCourses

WHERE month = 201807) THEN '〇'

ELSE '×' END AS "7 月",

CASE WHEN course_id IN

(SELECT course_id FROM OpenCourses

WHERE month = 201808) THEN '〇'

ELSE '×' END AS "8 月"

FROM CourseMaster;

-- 表的匹配：使用 EXISTS 谓词

SELECT CM.course_name,

CASE WHEN EXISTS

(SELECT course_id FROM OpenCourses OC

WHERE month = 201806

AND OC.course_id = CM.course_id) THEN '〇'

ELSE '×' END AS "6 月",

CASE WHEN EXISTS

(SELECT course_id FROM OpenCourses OC

WHERE month = 201807

AND OC.course_id = CM.course_id) THEN '〇'

ELSE '×' END AS "7 月",

CASE WHEN EXISTS

(SELECT course_id FROM OpenCourses OC

WHERE month = 201808

AND OC.course_id = CM.course_id) THEN '〇'

ELSE '×' END AS "8 月"

FROM CourseMaster CM;

这样的查询没有进行聚合，因此也不需要排序，月份增加的时候仅修改SELECT子句就可以了，扩展性比较好。

无论使用IN还是EXISTS，得到的结果是一样的，但从性能方面来说，EXISTS更好。通过EXISTS进行的子查询能够用到“month, course_id”这样的主键索引，因此当表OpenCourses里的数据比较多时，使用EXISTS的优势会更大。

作为表达式，CASE表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部；也正因为它是表达式，所以还可以写在SELECT子句、GROUP BY子句、WHERE子句、ORDER BY子句里。简单点说，在能写列名和常量的地方，通常都可以写CASE表达式。从这个意义上来说，与CASE表达式最接近的不是面向过程语言里的CASE语句，而是Lisp和Scheme等函数式语言里的case和cond这样的条件表达式。

关系数据库的近代史

在关系数据库出现之前，数据库市场的主流是基于层次模型的产品。顾名思义，层次模型以层次关系来表示数据之间的关系，使用程序来确定数据的位置以获取数据。现实中的“数据”大多拥有某种层次关系，如公司或学校等组织、组成机器的部件等，因此数据库就根据这些关系来表示数据。

这类数据库中具有代表性的产品是IBM公司的IMS(Information Management System)数据库。

在关系数据库中，所有的数据仅使用“表”这一种形式来表示。表看起来与“二维表”相似，对于用惯了微软公司的Excel或Google文档等电子表格的人来说，这种存储数据的方法非常直观。实际上，这种使用二维表来管理数据的方法在Excel等软件出现之前就已经很常见了，因此在关系数据库出现时，人们很容易就接受了它。

更重要的是，表还有一个具有划时代意义的特点，那就是在表中，数据的表示方法完全去除了“数据位置”的概念。因此，数据在表中是哪一行或哪一列没有任何含义。这一点与关系数据库出现之前的数据库和电子表格存在很大区别。

在关系操作中，应该将关系整体作为操作对象。这样做的目的是避免循环。显然，这是提高数据查询终端用户的生产效率的必要条件，同时也利于提高应用程序员的生产效率。

破坏性创新和引起它的破坏性技术，是哈佛大学商学院的克莱顿·克里斯坦森教授在其著作《创新者的窘境》中提出的著名的经营学概念，用来说明技术产品市场中发生典范转移的原因。所谓破坏性技术，就是该技术或产品如果按传统市场中的评价标准（大多是可靠性或性能）来评价会得到较差的结果，而如果按其他标准来评价，就会发现它有优于现有产品的地方（使用方便等）。破坏性技术的特征是通过吸引早期采用者来获得小规模的市场份额，具体示例有小型硬盘驱动器(HDD)、数码相机和智能手机等。

最开始，新技术会得到诸如“小玩具”“便宜没好货”等较低的评价，但随着质量的不断提高，即便按照旧的评价标准，它也能得到比现有主流产品更高的评价，这时市场份额就会发生戏剧性的逆转。这就是破坏性创新，也是解释拥有压倒性市场份额的优秀企业及其主力产品会被新兴企业及其（最初）看起来劣质的产品打败的有力理论。

关系型数据库遇到的问题

1、性能和可靠性的平衡

关系数据库比较突出的问题是性能问题。性能的构成元素不止一个，我们可以将其简单理解为“系统的处理速度”。数据库一直以来都在存储大量数据，但近年来，随着数据量的不断增大，关系数据库成为系统中最容易遇到瓶颈的地方。

关系数据库容易遇到瓶颈的原因主要有两个。一个原因是，为了统一管理数据，进行严格的事务管理，系统需要采用共享存储设备的结构，这使得存储设备会成为单点瓶颈。换句话说，就是“无法横向扩展”。

另一个原因是，SQL因其强大的表达能力和灵活性可以执行复杂的处理，特别是对大规模数据执行连接或子查询等复杂处理，但这会导致系统变得非常缓慢。

2、数据模型的局限性

关系数据库使用二维格式的表来表示数据。这是简单表示现实世界中大量数据的强大手段，但实际上，也有一些类型的数据很难通过这种手段来表示。其中，具有代表性的就是图和非结构化数据。

图是数学术语，相比其定义，我们还是看具体示例更容易理解。图通常包括组织图等树形图和表示社交网络服务(SNS)中用户间关系的网状图。前一种称为非循环图，后一种称为循环图。

正如我们前面看到的那样，表的格式是二维的，但层次结构是递归的。在平面二维表中，我们很难表示这种递归结构。

关于非结构化数据，相比其定义，我们也来看一下具体示例吧。其实，关系数据库的表中方便存储的数据（CSV等）最初被称为“结构化数据”，与此相对，不容易使用表来处理的数据则统称为“非结构化数据”。

具有代表性的非结构化数据是XML和JSON，它们都是互联网上交换数据时常用的格式。在这类数据中，各标签表示信息的规则是固定的，但各文档中包含的标签数量，以及一个标签持有的信息量等内容是不固定的（当然，在个别的商业规则中也有固定的，但通常是不固定的）。对于这类数据，使用面向过程语言中的循环和分支处理起来并不难，但使用事先要确定列的含义和数量的表就很困难了。这是因为，关系数据库中的表的结构从某种程度上来说是静态的，在系统运行过程中不会动态修改。改变表的定义或表之间的关联意味着要进行大规模的修改，所以越是经验丰富的工程师，处理起来就越谨慎。

NoSQL是用于解决前面介绍的性能和数据模型的问题。

解决问题的思路是以下两点

1、简化数据模型，限制复杂的数据操作

2、消除单点瓶颈，使其可横向扩展

性能问题的解决方案

实现第一种思路的典型NoSQL是KVS（Key-Value Store，键值存储）。我们也可以将作为数据模型的KVS看作一种表，其结构非常简单，包括“键”及其唯一确定的“值”。因此，KVS的用途是使用唯一的键来实现高速查找（但无法执行连接等高级处理）。

许多包含KVS的NoSQL产品使用多个数据库实例来组成集群，从而横向扩展、提高性能。

非结构化数据的解决方案

为了处理非结构化数据而出现的一种NoSQL类型称为面向文档的数据库（也称文档数据库）。这种数据库无须将JSON或XML等自由度高的文档转换为关系数据库的表就能够进行处理。这种类型的数据库产品有MongoDB、CouchDB等。另外，我们在前面还讲到关系数据库难以处理图这种数据模型，为了解决该问题，近年来人们也在不断开发图数据库，出现了Neo4j等产品。

为什么叫关系模型

表面上看数据库是由一个个表组成，那么为什么不称之为表模型呢？

以下是关系和表的区别：

1、关系中不允许存在重复的元组(tuple)，而表中可以存在。即，关系是通常说的不允许存在重复元素的集合，而表是多重集合(multiset)

2、关系中的元组没有从上往下的顺序，而表中的行有从上往下的顺序

3、关系中的属性没有从左往右的顺序，而表中的列有从左往右的顺序

4、关系中所有的属性的值都是不可分割的，而表中列的值是可以分割的。换句话说，关系中的属性满足第一范式，而表中的列不满足第一范式

实现了定义域的DBMS很少——这种说法严格来讲是不正确的。因为对于比较初级的定义域，正好相反，几乎所有的DBMS已经实现了。这些定义域主要是字符型、数值型等叫作标量类型的数据类型。因为标量类型对属性的取值范围有约束，所以尽管有局限性，但是它们也是定义域的一种。我们除了不能往声明为INTEGER型的列中插入abc这样的字符串以外，还可以使用CHECK约束，执行比针对声明为标量类型的列进行的约束更为严格的约束。例如，给声明为字符型的列加上约束，限制该列只能取值为 'm'和 'f'，就可以写成CHECK (sex IN ('m', 'f'))了。

这样看来数据类型就是一种范围很大的定义域了。

FROM子句中写的表名正是变量的名称。

地址：巨大的怪物

在一定程度上对使用者隐藏地址，确实也是编程语言的进步。但是深入到内部看的话，还是到处都充斥着如同洪水一般的地址。面向对象的方法也没能成为通用的杜绝地址泛滥的有效手段。因为对象仍然是由OID这样的地址来管理的，而且程序变得复杂后，对象会被大量生成，这样就和以往的面向过程语言中的大量声明变量没有什么区别了。

我们在编程时经常会用到的变量，正是编程语言中地址的化身。所有的变量都由没有实际意义的地址在管理着，而且要想在面向过程语言中处理数据，就必须把数据赋值给变量才行。只要使用变量，就无法逃出地址的魔咒。反过来说，SQL之所以能成为不依赖于地址的自由的语言，就是因为它不使用变量（及赋值）。习惯了面向过程语言的程序员刚开始接触SQL时肯定会想：“这门语言没有变量，用起来真不方便。”但是，SQL采用这种风格的意图非常明显。

GROUP BY和PARTITION BY

在SQL的功能中，GROUP BY和PARTITION BY非常相似——也可以说几乎一样。

它们的区别

SQL的语句中具有分组功能的是GROUP BY和PARTITION BY，它们都可以根据指定的列为表分组。区别仅仅在于，GROUP BY在分组之后会把每个分组聚合成一行数据。

例如，有下面这样一张存储了几个团队及其成员信息的表Teams。

对这张表使用GROUP BY或者PARTITION BY，可以获取以团队为单位的信息。

SELECT member, team, age , RANK()OVER(PARTITION BY team ORDER BY age DESC)rn, DENSE_RANK()OVER(PARTITION BY team ORDER BY age DESC)dense_rn, ROW_NUMBER()OVER(PARTITION BY team ORDER BY age DESC)row_num FROM Teams ORDER BY team, rn;

执行结果

接下来，我们重点关注一下划分出的子集，可以发现它们有下页这3个性质。

01.它们全都是非空集合。

02.所有子集的并集等于划分之前的集合。

03.任何两个子集之间都没有交集。

在数学中，满足以上3个性质的各子集称为“类”(partition)，将原来的集合分割成若干个类的操作称为“分类”。

在SQL中，GROUP BY的使用非常频繁，由此可以知道我们身边存在着很多类。例如学校中的班级和学生的出生地等。没有学生的班级是没有存在意义的，而出生地为两个省的人应该也是不存在的（出生地不详的人可能会有，但是这样的人应该属于列为NULL的类）。

扑克牌的卡片也一样。52张卡片根据花型可以分为4类，根据颜色可以分为红色和黑色两类。

从面向过程思维向声明式思维、面向集合思维转变的7个关键点

面向过程语言的思维方式是以赋值、条件分支、循环等作为基本处理单元，并将系统整体分割成很多这样的单元。它是将复杂的东西看成是由简单单元组合而成的——这是一种还原论的思维方式。

还原论是把高级现象还原成低级的基本现象的学说。

SQL的思维方式，从某种意义上来说刚好相反。SQL中没有赋值或者循环的处理，数据也不以记录为单位进行处理，而以集合为单位进行处理。SQL和关系数据库的思维方式更像是一种整体论的思维方式。

如果硬要以面向过程的方式写SQL语句，那么写出的SQL语句要么长且复杂、可读性不好，要么大量借助于存储过程和游标，又回到已经习惯的面向过程的世界。随着窗口函数的引入，虽然SQL也逐渐加入了面向过程的思维方式，但其根基还是声明式、面向集合的思维方式。

1、用CASE表达式代替IF语句和CASE语句。SQL更像一种函数式语言

在面向过程语言中，条件分支是以“语句”为单位进行的。而在SQL中，条件分支是以语句中的“表达式”为单位进行的。SQL还可以在一个SELECT语句或UPDATE语句中，表达与面向过程语言一样非常复杂而且灵活的条件分支，不过这需要借助CASE表达式。

之所以叫它CASE“表达式”而不是CASE“语句”(statement)，是因为CASE表达式与1+(2-4)或者 (x*y)/z一样，都是表达式，在执行时会被整体当作一个值来处理。既然同样是表达式，那么能写1+1这样的表达式的地方就都能写CASE表达式，而且因为CASE表达式最终会作为一个确定的值来处理，所以我们也可以把CASE表达式当作其他表达式或函数的参数来使用。

对于任意输入返回的都是一个值——从这个角度来说，我们还可以把CASE表达式看作一种函数。因此使用CASE表达式时的思维方式与使用函数式语言时是相似的。

参考⇨1-1节“CASE表达式”

2、用GROUP BY和窗口函数代替循环

SQL中没有专门的循环语句。虽然可以使用游标实现循环，但是这还是面向过程的做法，和纯粹的SQL没有关系。

SQL中没有循环，而且没有循环也不会带来什么问题，因为只要有集合运算和窗口函数，大多操作能够简洁表达且被高效执行。

参考 ⇨ 1-7节“用窗口函数进行行间比较”　　　

1-9节“用SQL进行集合运算”

3、表中的行没有顺序

在理解未知的概念时，我们首先会根据已经理解的概念去理解——这是一种行之有效的方法，也几乎是唯一的方法。但是在理解达到某种程度之后，我们必须放弃对旧概念的依赖。将表看成文件的最大问题就是“会误认为表中的行是有顺序的”。

对于文件来说，行的顺序是非常重要的。打开文本文件时，如果各行按照随机的顺序展示，那么文件是没法使用的。但是在关系数据库中，从表中读取数据时的的确确会发生这样的情况。读出的数据不一定是按照INSERT的顺序排列的，因为SQL在处理数据时不需要它们这样。SQL在处理数据时可以完全不依赖顺序。

关系数据库中的表（关系）是一种数学上的“集合”。表有意地放弃了“行的顺序”这一形象的概念，从而使它具有了更高的抽象度。文件和表原本就是不同的概念，两者之间自然会有些不一致。与其将表比作排列整齐的书架，还不如说它更像是随意堆放各种物品的“玩具箱”或者“袋子”。

学习新的概念时，我们需要暂时舍弃掉旧的概念，或者最起码要把旧的概念用括号括起来，再拿它与新的概念对比。这种学习方法并不新鲜，是长期以来一直被人们普遍使用的正面攻击法。但是，正面攻击法往往是最困难的。这是因为，在舍弃已经习惯了的风格时，我们需要的不只是理智，还有勇气。

参考⇨1-6节“HAVING子句的力量”

4、将表看成集合

虽然我们很容易把表看成与文件一样的东西，但是实际上，一张表并非对应一个文件，读取表时也并不是像读取文件一样一行一行地进行的。

理解表的抽象性的最好的方法是使用自连接。原因很显然，自连接本身就是基于集合这一高度抽象（也可以说成自由）的概念的技术。在SQL语句中，我们给同一张表赋予不同的名称后，就可以把这两张表当成不同的表来处理。也就是说，通过自连接，我们可以添加任意数量的集合来处理。这种高度自由正是SQL的魅力及力量所在。

参考⇨1-3节“自连接的用法”

5、理解EXISTS谓词和“量化”的概念

支撑SQL的基础理论，除了集合论，还有谓词逻辑，具体地说，是一阶谓词逻辑。

在SQL中，谓词逻辑的主要应用场景是“将多行数据作为整体来处理”的时候。谓词逻辑中具有能将多个对象作为一个整体来处理的工具“量化符”。对于SQL来说，量化符就是EXISTS谓词。

EXISTS的用法和IN很像，比较好理解。不过，我们更应该灵活掌握的其实是其否定形式——NOT EXISTS的用法。

说实话，使用NOT EXISTS的查询语句，可读性都不太好。而且，因为同样的功能也可以用HAVING子句或者ALL谓词来实现，所以很多程序员不太愿意使用它。但是，NOT EXISTS有一个很大的优点，即性能比HAVING子句和ALL谓词要好得多。

在优先考虑代码的可读性时，我们没必要强行使用NOT EXISTS来表达全称量化。但是也有需要优先考虑性能的时候。

参考⇨1-5节“EXISTS谓词的用法”

常见的SQL谓词包括但不限于：

1、比较运算符：如 =, <>, <, >, <=, >= 等，用于比较两个值。

2、BETWEEN...AND：用于判断某个值是否位于两个值之间（包含边界值）。

3、IN：用于检查一个值是否在一个指定的集合内。

4、LIKE：用于字符串模式匹配，支持通配符 % 和 _。

5、IS NULL：用于检查一个值是否为NULL。

6、EXISTS：用于检测子查询是否返回了任何记录。

7、ALL, ANY, SOME：这些关键字用于与比较运算符结合使用来比较一个值和一组值中的所有、任意或某些值。

6、学习HAVING子句的真正价值

HAVING子句是集中体现了SQL之面向集合理念的功能。

这样说的原因是，与WHERE子句不同，HAVING子句正是设置针对集合的条件的地方，因此为了灵活运用它，我们必须学会从集合的角度来理解数据。通过练习HAVING子句的用法，我们会在不经意间加深对面向集合这个本质的理解，真是一举两得。

参考⇨1-6节“HAVING子句的力量”

7、不要画长方形，去画圆

面向过程语言在不断发展的过程中积累了许多用于辅助编程的视觉工具。特别是产生于1970年并发展至今的结构图(structure diagram)和数据流图(data flow diagram)，它们已经成为业内的标准，并有着很好的效果。这些图一般都用长方形表示处理过程，用箭头表示数据的流转方向。

目前，能够准确描述静态数据模型的标准工具是维恩图，即“圆”。通过在维恩图中画嵌套子集，可以很大程度地加深对SQL的理解。这是因为，嵌套子集的用法是SQL中非常重要的技巧之一。例如，GROUP BY或者PARTITION BY将表分割成一些称为“类”的子集[插图]，以及冯·诺依曼型递归集合、用来处理树结构的嵌套子集模型，都是子集的代表性应用。能否深刻理解并灵活使用嵌套子集（=递归集合），可以说是衡量SQL编程能力是否达到中级水平的关键。

动作电影领域的“大神”李小龙曾说过一句名言：不要思考，去感受。同样，数据库领域的“大神”乔·塞尔科也说过类似的名言：不要画长方形和箭头，去画圆[插图]。这句话非常精辟。

参考 ⇨ 1-6节“HAVING子句的力量”

1-9节“用SQL进行集合运算”

必知必会的窗口函数

如果熟练掌握了窗口函数，从某种意义上来说，我们就可以像使用面向过程语言那样操作数据。窗口函数能够大幅扩展SQL编程的可能性，是一个非常重要的工具。

什么是窗口

匿名窗口

SELECT products_id, products_name, sale_price,

AVG (sale_price)OVER (ORDER BY products_id

ROWS BETWEEN 2 PRECEDING

AND CURRENT ROW) AS moving_avg

FROM Products;

上面的代码按商品ID的升序来排列商品表，计算包含当前ID之前的两个商品的价格移动平均值。虽然出现了AVG、OVER、ROWS BETWEEN、CURRENT ROW等窗口函数的关键字，但我们看不到窗口本身的定义。

有名称的窗口

SELECT products_id, products_name, sale_price,

AVG(sale_price)OVER W AS moving_avg

FROM Products

WINDOW W AS (ORDER BY products_id

ROWS BETWEEN 2 PRECEDING

AND CURRENT ROW);

这里显式定义了窗口，并对其应用了AVG函数。这里所说的窗口，就是针对通过FROM子句选择的记录集，使用ORDER BY排序和使用ROWS BETWEEN定义帧之后所形成的数据集。窗口会通过各种可选项对记录集进行数据加工，这就是它和记录集的不同之处。

通过比较这两种语法可以知道，我们常用的窗口函数的语法，是默认使用“匿名窗口”的简略版语法（这与匿名存储过程或匿名函数是一样的）。其优点是内容简练，而带名称的窗口的优点是窗口可以重复使用，能避免编辑错误。这与通过公用表表达式(CTE)重复使用视图，以及在存储过程中定义有名称的存储过程的效果是一样的。

匿名窗口和有名称的窗口各有优势，要根据具体情况进行选择，但有一点必须注意，即有的DBMS不支持有名称的窗口，一旦使用就会发生错误。人们通常认为有名称的窗口是“正式”的语法，但实际情况恰好相反，被普遍使用的是匿名窗口。这种语法上的不兼容会给DBMS之间的迁移带来风险，因此原则上要使用匿名窗口（在理解了有名称的窗口定义之后），这种做法或许更稳妥一些。

一张图看懂窗口函数

窗口函数让人难以理解的原因之一是1个窗口函数中包含多个操作，而如果像下图那样从整体来看，窗口函数实际上只包含下面3个功能。

01.使用PARTITION BY子句分割记录集合。

02.使用ORDER BY子句对记录排序。

03.使用帧子句定义以当前记录为中心的子集。

第1个功能和第2个功能因为与现有的GROUP BY和ORDER BY的功能几乎一样，所以对于已经掌握SQL基本语法的人来说都很容易理解。窗口函数真正特有的功能是上面列出的第3个功能。传统的SQL编程中并没有显式地使用“当前记录”的概念。另外，使用关系数据库构建过系统的人应该能立刻注意到，这个“当前记录”源自“游标”(cursor)的引入——关系数据库一直使用游标向面向过程语言传递数据（图1.2.2）。

之所以需要游标，是因为关系数据库的表中的记录是无序的，操作的基本单位是记录的集合，也就是一次一集合(set at a time)的操作方式，而面向过程语言的记录是有序的，操作的基本单位是一行记录，也就是是一次一记录(record at a time)的操作方式，我们需要用游标来填补二者之间的差异。

使用帧子句将其他行移至当前行

帧子句的作用是能通过SQL简单计算出移动平均值等以当前记录为基准计算的统计指标。除此之外，帧子句还有很广泛的用途。直观来讲，帧子句可以将其他行移至当前行。之前使用SQL进行行间比较很困难，现在则变得很自如。

求过去最临近的值

我们先来思考一下基本的时间序列分析。当比较时间序列中的数据时，SQL基本上是沿着时间序列，一行一行地向前追溯或向后推进。

以下图为例，这是一张存储测量日期和负载量的表

首先计算各行的“过去最临近的日期”，也就是计算“上一行”的日期。

SELECT sample_date AS cur_date,

MIN(sample_date)

OVER (ORDER BY sample_date ASC

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date

FROM LoadSample;

执行结果：

cur_date lastest_date

2018-02-01 00:00:00.000 NULL

2018-02-02 00:00:00.000 2018-02-01 00:00:00.000

2018-02-05 00:00:00.000 2018-02-02 00:00:00.000

2018-02-07 00:00:00.000 2018-02-05 00:00:00.000

2018-02-08 00:00:00.000 2018-02-07 00:00:00.000

2018-02-12 00:00:00.000 2018-02-08 00:00:00.000

该查询的重点是通过ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING将帧子句的范围限定在按sample_date排序后的上一行。一般来说，BETWEEN大多用来指定多行的范围，而这里用来将范围限定为一行，自然就不会发生错误。

PRECEDING是让帧的范围向前移动。

Q1 除向前移动之外，帧还可以向“后”移动吗？

使用FOLLOWING关键字。例如，在过去最临近的值的查询中，将帧的范围向后移动一行。

SELECT sample_date as cur_date,

min(sample_date)

over(order by sample_date

rows between 1 following and 1 following) as next_date

from LoadSample

执行结果：

cur_date next_date

2018-02-01 00:00:00.000 2018-02-02 00:00:00.000

2018-02-02 00:00:00.000 2018-02-05 00:00:00.000

2018-02-05 00:00:00.000 2018-02-07 00:00:00.000

2018-02-07 00:00:00.000 2018-02-08 00:00:00.000

2018-02-08 00:00:00.000 2018-02-12 00:00:00.000

2018-02-12 00:00:00.000 NULL

另外，同时使用PRECEDING和FOLLOWING，将当前记录夹在中间，还可以设置范围为“前后各 n行”的帧。

Q2 这里使用了MIN函数，请问它有什么含义呢

如果是像示例这样将帧的范围限定为一行，那么MIN并没有什么特别的含义。即使使用的是MAX、AVG或SUM，结果也是一样的，因为这相当于对一行应用聚合函数。如果帧的范围是多行，就需要应用相应的聚合函数了。

下面是帧子句中可以使用的选项，大家可以参考。

● ROWS：按行设置移动单位

● RANGE：按列值设置移动单位。使用ORDER BY子句来指定基准列

● n PRECEDING：仅向前（行号较小的方向）移动n行。n为正整数

● n FOLLOWING：仅向后（行号较大的方向）移动n行。n为正整数

● UNBOUNDED PRECEDING：一直移动到最前面

● UNBOUNDED FOLLOWING：一直移动到最后面

● CURRENT ROW：当前行

窗口函数的本质是排序

由刚才讲解的内容可知，窗口函数在内部对记录集合进行了排序。在笔者编写本书时（2018年），所有的DBMS都是如此。之所以要在窗口函数中进行排序，是出于使用PARTITION BY子句进行分组和使用ORDER BY子句对记录排序时的需要。在关系数据库中，表的记录并不一定是物理排序的，因此一般来说，如果要基于键值对记录排序，就需要先对记录集合进行排序[插图]。

为何是OVER，而不是ON

在窗口函数中，定义了使用PARTITION BY子句进行分割、使用ORDER BY子句进行排序这些操作的SQL语句，使用的关键字是OVER。如果没有这些SQL语句，AVG或SUM就不算是窗口函数，只能作为聚合函数来执行操作。因此，OVER可以说是窗口函数的标记。

众所周知，OVER在英语中是表示“在（某个对象的）上面”的介词。这里的“对象”当然就是记录集合。不过，ON也有“在上面”的意思，那么窗口函数为什么不使用ON呢？

由于SQL中已经将ON用作指定连接条件的关键字，所以不使用ON的直接原因或许是为了避免混淆。

人类的逻辑学

SQL采用的三值逻辑属于“非古典逻辑”这一比较新的逻辑学流派。在逻辑学的发展过程中，长期占据统治地位的是古典逻辑学。它以二值逻辑为前提，认为对于命题，我们一定能够判断真假。

历史上最早提出三值逻辑体系的是波兰的著名逻辑学家扬·武卡谢维奇(Jan Lukasiewicz，1878—1956)。他和提出模型论的阿尔弗雷德·塔尔斯基(Alfred Tarski，1902—1983)，以及斯坦尼斯瓦夫·列斯涅夫斯基(Stanislaw Lesniewski, 1886—1939)等著名的数学家一道，开启了战争期间波兰数学和哲学发展的黄金时期。函数式语言中用到的波兰式写法（把“3 + 2”写成“+32”）也是由他提出的，他的一些其他贡献直到现在仍然被广泛应用着。

在二十世纪二十年代，他定义了“真”和“假”之外的第三个逻辑值“可能”。此前的逻辑学中，命题取“真”“假”之外的其他值，根本就是无法想象的。当时的主流观念认为，如果命题是一种描述事实的语句，那么当然必须是有真假的。

如果阅读过武卡谢维奇的论文，我们会发现他用来表达第三个值的分类其实包含在科德提出的“未知”分类里。他曾举过这样一个例子：关于未来某个时间自己在哪里的陈述，我们现在既无法确定它为真，又无法确定它为假。完整的表述有点长，但是由于这段内容非常关键，所以这里就直接引用了。

我认为，明年的某一个时间点（比如12月21日正午）我是否在华沙，在今天这一天看来无法肯定也无法否定，这并不矛盾。因此在指定的时间点我也许在华沙这件事是可能的，但却不是必然的。进而，“明年的12月21日正午我也许在华沙”这个命题，在今天这一天看来既不可能是真也不可能是假。……因此，在今天这一天，这个命题的值只能是一个全新的值，不同于表示真的数值“1”，也不同于表示假的数值“0”。我们可以用“1/2”来表示这个值。它的含义是“可能”，它是和“真”“假”并列的第三个值。

在提出命题逻辑的三值逻辑体系的背后，有着上面这些思索。

这篇论文首次提出三值逻辑，非常有纪念意义。我们接下来解释一下其中需要特别注意的两个论点。关于第一个论点，我们从前文也可以看出，武卡谢维奇考虑的“可能”这一真值的本质，其实是对未来不确定性的描述，丝毫没有科德提出的“不适用”的含义。虽然不能断言，但是笔者认为，可能对于武卡谢维奇来说，科德认为“不适用”的那些命题是完全没有意义的，所以他根本不会考虑用真值来描述吧。

第二个论点突破了一个命题只能有一个固定真值的观念，开拓出了新的思路，认为命题的真值可能会随时间发生“可能”→“真”，或者“可能”→“假”这样的变化。这是站在传统逻辑学的立场上无法想象的革命性的（或者说是超越常识的）的思考方式。虽然武卡谢维奇自身并没有写明其思考延伸到了这么细致的地方，但是他确实表达过相近的观点——他认为命题的作用其实不在于表达事实，而在于反映人们对这件事实的认知。按照这个观点理解，命题其实不存在于客观世界，而是存在于我们的内心。

从提出这样一个心理学式命题理论的贡献来看，逻辑学家武卡谢维奇确实可以说是科德的前辈，为关系数据库奠定了理论基础。

消灭NULL委员会

为什么NULL如此惹人讨厌

NULL惹人讨厌的原因数不胜数，其中具有代表性的原因有如下几个。

01.在进行SQL编码时，必须考虑违反人类直觉的三值逻辑。

02.在指定ISNULL、IS NOTNULL的时候，限制使用索引，执行起来性能低下。在许多DBMS中，如果索引中有很多NULL，则索引不会被引用，有的实现根本不使用索引，如Oracle。

03.如果四则运算以及SQL函数的参数中包含NULL，会引起“NULL的传播”。

04.在接收SQL查询结果的宿主语言中，NULL的处理方法没有统一标准。另外，各DBMS的NULL处理规范也不统一。

05.与一般列的值不同，NULL是通过在数据行的某处加上多余的位(bit)来实现的。因此NULL会使程序占据更多的存储空间，使得检索性能变差。

06.在包含NULL的列中创建唯一索引的“唯一”在每个RDBMS中的含义也不一样。例如，在包含多个NULL的列中创建唯一索引时，针对重复的NULL，有的会发生错误，有的不会发生错误。

07.由于NULL并不是值，所以在使用ORDER BY语句进行排序时，我们需要注意排序规则。因为NULL并不是定义域中包含的值，所以本不可以进行排序。而在实际业务中，我们必须将其显示在报告中的某处，因此，我们通常将其作为最大值或最小值来处理。根据具体实现，默认是最大值还是最小值也不一样，这就变得更加复杂了.

消除NULL的具体做法

编号：使用异常编号

大家使用的数据库表中一定存储了各种各样的编号。例如，企业编号、顾客编号、行政区划编号、性别编号，等等。像性别这样更多地被称作“标志”的属性，我们也可以从广义上把它看成一种编号。标志这类编号通常只用来表示两个值。像这类编号，一般都用于表中重要的列，很多时候会作为搜索和连接的列来使用。因此，我们当然要把它作为消除NULL的首要目标。

解决方法很简单，分配异常编号就行了。例如ISO的性别编号中，除了“1:男性”“2:女性”，还定义了“0:未知”“9:不适用”这两个用于异常情况的编号。编号9可用于法人的情况。这真是一种很棒的解决方案，无意间刚好与由科德区分的两类NULL“未知”和“不适用”相吻合了。

并不是所有的情况都必须预留这两个编号，很多时候，有一个就足够使用了。例如当必须往数据库中插入编号未知的顾客信息时，定义一个表示未知的编号“×××××”就可以了。需要说明的是，请尽量避免使用“99999”这样的编号作为异常编号。因为当编号个数很多的时候，使用数字的话，有可能会出现用来表示异常的编号和真实的顾客编号重复的情况。因此，编号列应该使用字符串类型。笔者偶尔会看到一些将编号列定义为数值型的表，这令笔者感到很难过。

名字：使用“无名氏”

大家使用的数据库表中一定存储了数量不亚于编号的各种各样的名字。在使用名字的时候，处理方法和编号是一样的。也就是说，赋予表示未知的值就可以了。不论是“未知”还是“UNKNOWN”，只要是在开发团队内部达成一致的适当的名字就行。

一般来说，与编号相比，名字被用于聚合的频度很低，大多时候只作为冗余列使用[插图]。我们不用刻意地消除其中的NULL，但是最好还是让NULL从名字列中消失。

数值：用0代替

对于数值型的列，笔者认为最好的方法是一开始就将NULL转换为0再存储到数据库中。如果允许NULL，那么就必须在统计数据时使用NULLIF函数或者IS NOT NULL谓词来排除NULL，笔者不推荐这样来做。从笔者的经验来看，将NULL转换成0从来没有带来过任何问题，而且消除NULL带来的好处有很多。

日期：用最大值或最小值代替

对于日期，NULL的含义存在多种可能性，需要根据具体情况决定是使用默认值还是使用NULL。

当需要表示开始日期和结束日期这样的“期限”的时候，我们可以使用0000-01-01或者9999-12-31这样可能存在的最大值或最小值来处理。例如表示员工的入职日期或者信用卡的有效期的时候，就可以这样处理。这种方法一直都被广泛使用着。

相反，当默认值原本就不清楚的时候，例如历史事件发生的日期，或者某人的生日等，也就是当NULL的含义是“未知”的时候，我们不能像前面那样设置一个有意义的默认值。这时可以允许使用NULL。

SQL中的层级

在SQL中，使用GROUP BY聚合之后，我们就不能引用原表中除聚合键之外的列。对于不习惯SQL的程序员来说，这个规则很让人讨厌，甚至被认为是多余的。但是，其实这只是SQL中的一种逻辑，是为了严格区分层级。本节就从这个乍一看不可思议的现象讲起，逐步带大家接近SQL的本质。

为什么聚合后不能再引用原表中的列

标准SQL规定，在对表进行聚合查询的时候，只能在SELECT子句中写3种内容。

01.通过GROUP BY子句指定的聚合键

02.聚合函数（SUM、AVG等）

03.常量

SQL的初学者大多会忽略这条约束，从而犯下在聚合查询时往SELECT子句中加入多余列的错误。他们会在不断出错的过程中慢慢地习惯，并在不经意间学会正确的写法，但是很少有人能正确地理解为什么会有这样的约束。当大家被新入职的下属程序员们问到“为什么不能把表中的很多列写在SELECT子句中”时，有没有觉得无从解释呢？

其实，这里隐藏了一个与本节主题紧密相连的问题。表Teams中的age列存储了每位成员的年龄信息，但是需要注意的是，这里的年龄只是每个人的属性，而不是小组的属性。所谓小组，指的是由多个人组成的集合，因此小组的属性只能是平均或者总和等统计性质的属性。

询问每个人的年龄是可以的，但是询问由多个人组成的小组的年龄就没有意义了。对于小组来说，只有“平均年龄是多少？”或者“最大身高是多少？”这样的问法才是有意义的。强行将适用于个体的属性套用于团体之上，纯粹是一种分类错误。

MySQL会忽略掉层级的区别，因此这样的语句执行起来也不会出错。可能对用户来说这样会比较舒服，但实际上它违背了SQL的基本原理。使用GROUP BY聚合之后，SQL的操作对象便由0阶的“行”变为了1阶的“行的集合”。此时，行的属性便不能使用了。SQL的世界其实是层级分明的等级社会。将低阶概念的属性用在高阶概念上会导致秩序的混乱，必须遭到惩罚。

WHERE子句用于处理“行”这种0阶的对象，而HAVING子句用来处理“集合”这种1阶的对象。

三值逻辑和NULL

为什么必须写成“IS NULL”，而不是“＝NULL”

应该有不少人对上面这个标题里的问题感到困惑吧？相信刚学SQL的时候，大部分人有过这样的经历：写了下面这样的SQL语句来查询某一列中值为NULL的行，结果却执行失败了。

-- 查询 NULL 时出错的 SQL 语句

SELECT *

FROM tbl_A

WHERE col_1 = NULL;

通过这条SQL语句，我们无法得到正确的结果。因为正确的写法是col_1 IS NULL。这个错误和有些人刚学C、Java、Python等语言时写出的if(hoge=0)错误非常相似。那么，为什么在SQL中用“=”去进行比较会失败呢？表示相等关系时用“=”，这明明是我们在小学里就学过的常识。

这当然是有原因的。那就是，对NULL使用比较谓词后得到的结果总是unknown。查询结果只会包含WHERE子句里的判断结果为true的行，不会包含判断结果为false和 unknown的行。不只是等号，对NULL使用其他比较谓词，结果也都是一样的。所以无论col_1是不是NULL，比较结果都是unknown。

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

“列的值为NULL”“NULL值”这样的说法本身就是错误的。因为NULL不是值，所以它原本就不在定义域(domain)中。如果有人认为NULL是值，那么笔者倒想请教一下：它是什么类型的值？关系数据库中存在的值必然属于某种类型，比如字符型或数值型等。因此，假如NULL是值，那么它就必须属于某种类型。

CASE表达式和NULL

下面，我们来看一下在CASE表达式里将NULL作为条件使用时经常会出现的错误。首先，请看下面的简单CASE表达式。

--col_1 为 1 时返回〇、为 NULL 时返回 × 的 CASE 表达式？

CASE col_1

WHEN 1 THEN '〇'

WHEN NULL THEN '×'

END

这个CASE表达式一定不会返回 ×。这是因为，第二个WHEN子句是col_1=NULL的缩写形式。正如大家所知，这个式子的真值永远是unknown。而且CASE表达式的判断方法与WHERE子句一样，只认可真值为true的条件。正确的写法是像下面这样使用搜索CASE表达式。

CASE WHEN col_1 = 1 THEN '〇'

WHEN col_1 IS NULL THEN '×'

END

这种错误很常见，其原因是将NULL误解成了值。这一点从NULL和第一个WHEN子句里的1写在了同一列就可以看出。这里请大家再次确认自己已经记住“NULL并不是值”这一点。

限定谓词和NULL

SQL里有ALL和ANY两个限定谓词。因为ANY与IN是等价的，所以人们不经常使用ANY。在这里，我们主要看一下更常用的ALL的一些注意事项。

ALL可以和比较谓词一起使用，用来表达“与所有的 ×× 都相等/不相等”，或“比所有的 ×× 都大”，或“比所有的xx都小”的意思。接下来，我们给B班表里为NULL的列填上具体的值。然后，使用这张新表来思考一下用于查询“比B班住在东京的所有学生年龄都小的A班学生”的SQL语句。

使用ALL谓词时，SQL语句可以像下面这样写

SELECT *

FROM Class_A

WHERE age < ALL ( SELECT age

FROM Class_B

WHERE city = '东京' );

查询结果：

拉里 19 琦玉

查询到的只有比山田年龄小的拉里，到这里都没有问题。但是如果山田年龄不详，就会有问题了：凭直觉来说，此时查询到的可能是比22岁的齐藤年龄小的拉里和伯杰，然而这条SQL语句的执行结果还是空。这是因为，ALL谓词其实是多个以AND连接的逻辑表达式的省略写法。具体的分析步骤如下所示。

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

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

使用极值函数代替ALL谓词的人应该不少吧。如果用极值函数重写刚才的SQL，应该是下面这样。

-- 查询比 B 班住在东京的年龄最小的学生还要小的 A 班学生

SELECT *

FROM Class_A

WHERE age < ( SELECT MIN(age)

FROM Class_B

WHERE city = '东京' );

执行结果：

拉里 19 埼玉

伯杰 21 千叶

没有问题。即使山田的年龄无法确定，这段代码也能查询到拉里和伯杰两人。这是因为，极值函数在统计时会把为NULL的数据排除掉。使用极值函数能使Class_B这张表里看起来就像不存在NULL一样。

但是，当谓词（或者函数）的输入为空集的情况。例如，Class_B这张表为如下所示的情况。

如上表所示，B班里没有学生住在东京。这时，使用ALL谓词的SQL语句会查询到A班的所有学生。然而，用极值函数查询时一行数据都查询不到。这是因为，极值函数在输入为空表（空集）时会返回NULL。因此，使用极值函数的SQL语句会按照下面的步骤被执行。

--1. 极值函数返回 NULL

SELECT *

FROM Class_A

WHERE age < NULL;

--2. 对 NULL 使用“<”后结果为 unknown

SELECT *

FROM Class_A

WHERE unknown;

比较对象原本就不存在时，根据业务需求有时需要返回所有行，有时需要返回空集。需要返回所有行时（感觉这类似于“不战而胜”），需要使用ALL谓词，或者使用COALESCE函数将极值函数返回的NULL处理成合适的值。

EXISTS谓词的用法

EXISTS不仅可以将多行数据作为整体来表达高级的条件，而且使用关联子查询时性能仍然非常好，这对SQL来说是不可或缺的功能。

什么是谓词

SQL的保留字中，有很多被归为谓词一类。例如，“=、<、>”等比较谓词，以及BETWEEN、LIKE、IN、IS NULL等。在写SQL语句时我们几乎离不开这些谓词，那么到底什么是谓词呢？几乎每天都在用，但是突然被问起来时却答不上来的人应该不少吧。

用一句话来说，谓词就是函数。当然，谓词与SUM或AVG这样的函数并不一样，否则就无须再分出谓词这一类，而是统一都叫作函数了。

实际上，谓词是一种特殊的函数，其返回值是真值。前面提到的每个谓词，返回值都是 true、false或者 unknown（一般的谓词逻辑里没有unknown，但是SQL采用的是三值逻辑，因此具有三种真值）。

在关系数据库里，表中的一行数据可以看作一个命题。

例如，这张表里第一行数据就可以认为表示这样一个命题：田中性别是男，而且年龄是28岁。表常常被认为是行的集合，但从谓词逻辑的观点看，也可以认为它是命题的集合（=陈述句的集合）。C.J.戴特曾经这样调侃过：数据库这种叫法有点名不副实，它存储的与其说是数据，还不如说是命题。

同样，我们平时使用的WHERE子句，其实也可以看成由多个谓词组合而成的新谓词。只有能让WHERE子句的返回值为真的命题，才能从表（命题的集合）中查询到。

实体的阶层

同样是谓词，但是与 =、BETWEEN等相比，EXISTS的用法还是大不相同的。概括来说，区别在于“谓词的参数可以取什么值”。

即入参不同

从上图和前文我们可以知道，EXISTS的特殊性在于输入值的阶数（输出值和其他谓词一样，都是真值）。谓词逻辑中，我们可以根据输入值的阶数对谓词进行分类。=或者BETWEEN等输入值为一行的谓词叫作“一阶谓词”，而像EXISTS这样输入值为行的集合的谓词叫作“二阶谓词”。阶(order)是用来区分集合或谓词的阶数的概念。

EXISTS因接受的参数是集合这样的一阶实体(entity)而被称为二阶谓词，但是谓词也是函数的一种，因此我们也可以说EXISTS是高阶函数。

查询表中“不”存在的数据

我们从数据库中查询数据时，一般是从表里存在的数据中选出满足某些条件的数据。但是在有些情况下，我们不得不从表中查找出“不存在的数据”。这听起来可能很奇怪，但是这种需求并不算少。例如下面这样的情况，大家是不是也遇到过呢？

显然，从这张表中求出“参加了某次会议的人”是很容易的。但是，如果反过来求“没有参加某次会议的人”，该怎么做呢？例如，伊藤参加了第1次会议和第2次会议，但是没有参加第3次会议；坂东没有参加第2次会议。

我们并不是要根据存在的数据查询“满足这样那样条件”的数据，而是要查询“数据是否存在”。从阶层上来说，这是更高一阶的问题，即所谓的“二阶查询”。这种时候正是EXISTS谓词大显身手的好时机。思路是先假设所有人都参加了全部会议，并以此生成一个集合，然后从中减去实际参加会议的人。这样就能得到缺席会议的人。

所有人都参加了全部会议的集合可以通过下面这样的交叉连接来求得。

结果是3（次）× 4（人），一共12行数据。然后，我们从这张表中减掉实际参会者的集合，即表Meetings中存在的组合即可。

-- 求出缺席者的 SQL 语句(1)：存在量化的应用

SELECT DISTINCT M1.meeting, M2.person

FROM Meetings M1 CROSS JOIN Meetings M2

WHERE NOT EXISTS

(SELECT *

FROM Meetings M3

WHERE M1.meeting = M3.meeting

AND M2.person = M3.person);

如上所示，我们的需求被直接翻译成了SQL语句，意思很好理解。这道例题还可以用集合论的方法来解答，即像下面这样使用差集运算。

---- 求出缺席者的 SQL 语句(2)：使用差集运算

SELECT M1.meeting, M2.person

FROM Meetings M1, Meetings M2

EXCEPT

SELECT meeting, person

FROM Meetings;

通过以上两条SQL语句的比较，我们可以明白，NOT EXISTS直接具备了差集运算的功能。

HAVING子句的力量

SQL给人感觉与众不同的原因有两个：第一个原因是，它是一种基于“面向集合”思想设计的语言，同样具备这种设计思想的语言很少；第二个原因的影响力不亚于第一个，即人最开始学习过某种理念下的语言后，心理上会形成思维定式，从而很难理解另一种理念下的语言。

查询不包含NULL的集合

COUNT函数的使用方法有COUNT(*)和COUNT（列名）两种，它们的区别有两个：第一个是性能上的区别；第二个是COUNT(*)可以用于NULL，而COUNT（列名）与其他聚合函数一样，要先排除掉NULL的行再进行统计。第二个区别也可以这么理解：COUNT(*)查询的是所有行的数目，而COUNT（列名）查询的不一定是。

对于这两个区别，我们在编写SQL语句时当然要多加留意，但是如果能好好利用，它们也可以发挥令人意想不到的作用。例如，这里有一张存储了学生提交报告的日期的表Students。

学生提交报告后，“提交日期”列会被写入日期，而提交之前它们是NULL。现在，我们需要从这张表里找出哪些学院的学生全部都提交了报告（即理学院、经济学院）。如果只是用WHERE sbmt_date IS NOT NULL这样的条件查询，文学院也会被包含进来，结果就不正确了（因为文学院学号为102的学生还没有提交）。正确的做法是，以“学院”为GROUP BY的列生成图1.6.3这样的子集。