神奇的 SQL 之谓词 → 尽量让你们理解 EXISTS

开心一刻

十年前，我：我交女票了，比我大两岁

妈：不行！赶紧分！

八年前，我：我交女票了，比我小两岁，外地的

妈：你就不能让我省点心？

五年前，我：我交女票了，市长的女儿

妈：别人还能看上你？分了吧！

今年，我挺着大肚子踏进家门

妈：闺女啊，你终于开窍了!

不服气吗.gif

谓词

SQL 中的谓词指的是：返回值是逻辑值的函数

我们知道函数的返回值有可能是数字、字符串或者日期等等，但谓词的返回值全部是逻辑值（true/false/unknown），谓词是一种特殊的函数

此时你们是不是有疑问：逻辑值不是只有 true 和 false 吗，哪来的 unknown ？

那不巧了吗，我正好有说明：神奇的 SQL 之温柔的陷阱 → 为什么是 IS NULL 而非＝ NULL ？

640 (7).jpg

里面就讲到了三值逻辑，你们一定要去阅读，方便后续的理解

SQL 中的谓词有很多，如 =、>、<、<> 等，我们来看看 SQL 具体有哪些常用的谓词

比较谓词

创建表与初始化数据

sql复制代码-- 1、表创建并初始化数据
DROP TABLE IF EXISTS tbl_student;
CREATE TABLE tbl_student (
  id INT(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno VARCHAR(12) NOT NULL COMMENT '学号',
    name VARCHAR(5) NOT NULL COMMENT '姓名',
    age TINYINT(3) NOT NULL COMMENT '年龄',
  sex TINYINT(1) NOT NULL COMMENT '性别，1:男,2:女',
  PRIMARY KEY (id)
);
INSERT INTO tbl_student(sno,name,age,sex) VALUES
('20190607001','李小龙',21,1),
('20190607002','王祖贤',16,2),
('20190608003','林青霞',17,2),
('20190608004','李嘉欣',15,2),
('20190609005','周润发',20,1),
('20190609006','张国荣',18,1);

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  cno varchar(5) NOT NULL COMMENT '班级号',
  cname varchar(20) NOT NULL COMMENT '班级名',
  PRIMARY KEY (`id`)
) COMMENT='学生班级表';
INSERT INTO tbl_student_class VALUES 
('1', '20190607001', '0607', '影视7班'),
('2', '20190607002', '0607', '影视7班'),
('3', '20190608003', '0608', '影视8班'),
('4', '20190608004', '0608', '影视8班'),
('5', '20190609005', '0609', '影视9班'),
('6', '20190609006', '0609', '影视9班');

SELECT * FROM tbl_student;
SELECT * FROM tbl_student_class;

相信你们对 =、>、<、<>（!=）等比较运算符都非常熟悉，它们的正式名称就是 比较谓词，使用示例如下

sql复制代码-- 比较谓词示例
SELECT * FROM tbl_student WHERE name = '王祖贤';
SELECT * FROM tbl_student WHERE age > 18;
SELECT * FROM tbl_student WHERE age < 18;
SELECT * FROM tbl_student WHERE age <> 18;
SELECT * FROM tbl_student WHERE age <= 18;

LIKE

对于 LIKE，我相信你们也非常熟悉

当我们想用 SQL 做一些简单的模糊查询时，都会用到 LIKE 谓词，分为前一致、中一致和后一致，使用示例如下

sql复制代码-- LIKE谓词
SELECT * FROM tbl_student WHERE name LIKE '李%';         -- 前一致
SELECT * FROM tbl_student WHERE name LIKE '%青%';        -- 中一致
SELECT * FROM tbl_student WHERE name LIKE '%青';         -- 后一致

如果 name 字段上建了索引，那么前一致会利用索引，而中一致、后一致会全表扫描

BETWEEN

当我们想进行范围查询时，往往会用到 BETWEEN 谓词，示例如下

1
2
3

sql复制代码-- BETWEEN谓词
SELECT * FROM tbl_student WHERE age BETWEEN 15 AND 22;
SELECT * FROM tbl_student WHERE age NOT BETWEEN 15 AND 22;

BETWEEN 和它之后的第一个 AND 组成一个范围条件

BETWEEN 会包含临界值 15 和 22

BETWEEN 可以比较谓词等价替换

1
2
3

sql复制代码SELECT * FROM tbl_student WHERE age BETWEEN 15 AND 22;
-- 等价于
SELECT * FROM tbl_student WHERE age >= 15 AND age <= 22;

若不想包含临界值，那就需要这么写了

1	sql复制代码SELECT * FROM tbl_student WHERE age > 15 AND age < 22;

IS NULL 和 IS NOT NULL

关于 NULL，不是一言两语能说清的，她的水很深，深的让你又爱又恨！

依旧很巧，我对她已经进行了很深入的研究：神奇的 SQL 之温柔的陷阱 → 为什么是 IS NULL 而非＝ NULL ？

你们一定要去仔细观摩，“姿势” 很丰富哟！

IN

有这样一个需求：查询出年龄等于 15、18以及20的学生，我们会用 OR 来查

1 2	sql复制代码-- OR SELECT * FROM tbl_student WHERE age = 15 OR age = 18 OR age = 20;

用 OR 来查没问题，但是有一点不足，如果选取的对象越来越多，SQL 会变得越来越长，阅读性会越来越差，此时我们可以用 IN 来代替

1 2	sql复制代码-- IN SELECT * FROM tbl_student WHERE age IN(15,18,20);

IN 有一种其他谓词没有的使用方法：使用子查询作为其参数

这个在平时项目中也是用的非常多的，例如：查询出影视7班的学生信息

sql复制代码-- IN 可以实现，但不推荐
SELECT * FROM tbl_student 
WHERE sno IN (
    SELECT sno FROM tbl_student_class 
    WHERE cname = '影视7班'
); 

-- 联表查，推荐
SELECT ts.* FROM
tbl_student_class tsc LEFT JOIN tbl_student ts ON tsc.sno = ts.sno
WHERE tsc.cname = '影视7班';

很多情况下，IN 是可以用联表查询来替换的

上面讲的 谓词，你们肯定都会，而且觉得非常简单

640 (11).jpg

但接下来要讲的，你们还会觉得简单吗

EXISTS

首先 EXISTS 也是 SQL 谓词，那为什么不放到 谓词 那一章节下来讲？

因为 EXISTS 是主角嘛，主角，你们懂吗

我是主角.jpg

主角最大嘛，戏份必须给足！

关于 EXISTS，我们平时用的不多，甚至不用，不是说它适用场景少，而是它走的 海王海女 这种高端路线，我们很难驾驭！

它用法与其他谓词不一样，而且不好理解，另外很多情况下可以用 IN 来代替

但今天，我也带你们高端一回，体验下 海王海女 的感觉

在真正讲解 EXSITS 示例之前，我们先来了解下理论知识：实体的阶层、全称量化与存在量化

实体的阶层

关于 阶，我只能说很润，润到你心坎的那种

很润.gif

不信的话，你们可以看看：神奇的 SQL 之层级 → 为什么 GROUP BY 之后不能直接引用原表中的列

SQL 严格区分阶层，不能跨阶层操作

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

例如 x = y 或 x BETWEEN y 等谓词可以取的参数是像 21 或者 李小龙 这样的单一值，我们称之为 标量值，而 EXISTS 可以取的参数究竟是什么呢？从下面这条 SQL 语句来看，EXISTS 的参数不像是单一值

sql复制代码SELECT * FROM tbl_student ts
WHERE EXISTS (
    SELECT * FROM tbl_student_class tsc
    WHERE ts.sno = tsc.sno
);

我们可以看出， EXISTS 的参数是行数据的集合

之所以这么说，是因为无论子查询中选择什么样的列，对于 EXISTS 来说都是一样的

在 EXISTS 的子查询里， SELECT 子句的列表可以有下面这三种写法

1
2
3

html复制代码通配符：SELECT *
常量：SELECT '1'
列名：SELECT tsc.id

也就是说如下 3 条 SQL 查到的结果是一样的

sql复制代码-- SELECT *
SELECT * FROM tbl_student ts
WHERE EXISTS (
    SELECT * FROM tbl_student_class tsc
    WHERE ts.sno = tsc.sno
);
-- SELECT 常量
SELECT * FROM tbl_student ts
WHERE EXISTS (
    SELECT 1 FROM tbl_student_class tsc
    WHERE ts.sno = tsc.sno
);
-- SELECT 列名
SELECT * FROM tbl_student ts
WHERE EXISTS (
    SELECT tsc.sno FROM tbl_student_class tsc
    WHERE ts.sno = tsc.sno
);

用个图来概括下 一般谓词 与 EXISTS 的区别

谓词区别.png

从上图我们知道，EXISTS 的特殊性在于输入值的阶数(输出值和其他谓词一样，都是逻辑值）

谓词逻辑中，根据输入值的阶数对谓词进行分类，= 或者 BETWEEEN 等输入值为一行的谓词叫作 一阶谓词，而像 EXISTS 这样输入值为行的集合的谓词叫作 二阶谓词，是不是高端起来了？

有点东西.gif

全称量化和存在量化

谓词逻辑中有量词（限量词、数量词）这类特殊的谓词，我们可以用它们来表达一些这样的命题：所有的 x 都满足条件 P 或者 存在（至少一个）满足条件 P 的 x ，前者称为 全称量词，后者称为 存在量词，分别记作 ∀（A的下倒）、∃（E的左倒）

SQL 中的 EXISTS 谓词实现了谓词逻辑中的 存在量词，然而遗憾的是， SQL 却并没有实现 全称量词

但是没有全称量词并不算是 SQL 的致命缺陷，因为全称量词和存在量词只要定义了一个，另一个就可以被推导出来，具体可以参考下面这个等价改写的规则（德·摩根定律）

1
2

html复制代码∀ x P x = ¬ ∃ x ¬P（所有的 x 都满足条件 P ＝不存在不满足条件 P 的 x ）
∃ x P x = ¬ ∀ x ¬Px（存在 x 满足条件 P ＝并非所有的 x 都不满足条件 P）

因此在 SQL 中，为了表达全称量化，需要将 所有的行都满足条件 P 这样的命题转换成 不存在不满足条件 P 的行

不知道你们看懂了，我反正已经讲晕了

太绕.gif

对理论晕了，你们先别慌，我们结合具体的实际案例来看看 EXISTS 的妙用

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

tbl_student 中的学生都分配到了具体的班级，假设新来了两个学生（刘德华、张家辉），他们暂时还未被分配到班级，我们如何将他们查询出来（查询未被分配到班级的学生信息）

sql复制代码-- 新来、未被分配到班级的学生
INSERT INTO tbl_student(sno,name,age,sex) VALUES
('20190610010','刘德华',55,1),
('20190610011','张家辉',46,1);

我们最容易想到的 SQL 肯定是下面这条

1 2	sql复制代码-- NOT IN 实现 SELECT * FROM tbl_student WHERE sno NOT IN(SELECT sno FROM tbl_student_class);

其实用 NOT EXISTS 也是可以实现的

sql复制代码-- NOT EXISTS 实现
SELECT * FROM tbl_student ts
WHERE NOT EXISTS (
    SELECT * FROM tbl_student_class tsc WHERE ts.sno = tsc.sno
);

肯定 ⇔ 双重否定转换

EXISTS 谓词来表达全称量化，这是 EXISTS 的用法中很具有代表性的一个用法

但是需要我们打破常规思维，习惯从全称量化 所有的行都×× 到其双重否定 不××的行一行都不存在 的转换

假设我们有学生成绩表：tbl_student_score

sql复制代码-- 学生成绩表
DROP TABLE IF EXISTS tbl_student_score;
CREATE TABLE tbl_student_score (
  id INT(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno VARCHAR(12) NOT NULL COMMENT '学号',
    subject VARCHAR(5) NOT NULL COMMENT '课程',
    score TINYINT(3) NOT NULL COMMENT '分数',
  PRIMARY KEY (id)
);
INSERT INTO tbl_student_score(sno,subject,score) VALUES
('20190607001','数学',100),
('20190607001','语文',80),
('20190607001','物理',80),
('20190608003','数学',80),
('20190608003','语文',95),
('20190609006','数学',40),
('20190609006','语文',90),
('20190610011','数学',80);

SELECT * FROM tbl_student_score;

所有科目分数都在 50 分以上

查询所有科目分数都在 50 分以上的学生，这个 SQL 怎么写？

是不是有点懵，懵就对了，不然你们不会往下看了呀！

我们需要转换下命题，将查询条件 所有科目分数都在 50 分以上 转换成它的双重否定没有一个科目分数不满 50 分，然后用 NOT EXISTS 来表示转换后的命题

sql复制代码-- 没有一个科目分数不满 50 分
SELECT DISTINCT sno
FROM tbl_student_score tss1
WHERE NOT EXISTS -- 不存在满足以下条件的行
(    SELECT * FROM tbl_student_score tss2
    WHERE tss2.sno = tss1.sno
    AND tss2.score < 50    -- 分数不满50 分的科目
);

是不是很简单？

数学分数在80分及以上且语文分数在50分及以上

查询出数学分数在 80 分以上（包含80）且语文分数在 50 分以上（包含）的学生，这 SQL 又该如何写？

这个条件是 全称量化 的条件吗

直观感觉不是，但如果改成：某个学生的所有行数据中，如果科目是数学，则分数在 80 分及以上；如果科目是语文，则分数在 50 分及以上

这是不是就是 全称量化 条件了？

接下来怎么办，肯定是进行双重否定转换呀，条件则是：某个学生的所有行数据中，如果科目是数学，则分数不低于 80；如果科目是语文，则分数不低于 50

那么我们就可以按如下顺序逐步写入满足条件的 SQL

sql复制代码-- 1、CASE 表达式，肯定
CASE WHEN subject = '数学' AND score >= 80 THEN 1
        WHEN subject = '语文' AND score >= 50 THEN 1
        ELSE 0 
END;

-- 2、CASE 表达式，单重否定（加上 NOT EXISTS才算双重）
CASE WHEN subject = '数学' AND score < 80 THEN 1
        WHEN subject = '语文' AND score < 50 THEN 1
    ELSE 0 
END;

-- 3、结果包含了 20190610011 的 SQL 
SELECT DISTINCT sno
FROM tbl_student_score tss1
WHERE subject IN ('数学', '语文')
AND NOT EXISTS
(
    SELECT *FROM tbl_student_score tss2
    WHERE tss2.sno = tss1.sno
    AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
                        WHEN subject = '语文' AND score < 50 THEN 1
                        ELSE 0 
                    END
);

-- 4、20190610011 没有语文成绩，剔除掉
SELECT sno
FROM tbl_student_score tss1
WHERE subject IN ('数学', '语文')
AND NOT EXISTS
(
    SELECT * FROM tbl_student_score tss2
    WHERE tss2.sno = tss1.sno
    AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
                        WHEN subject = '语文' AND score < 50 THEN 1
                        ELSE 0 
                        END
)
GROUP BY sno
HAVING COUNT(*) = 2; -- 必须两门科目都有分数

我相信你们肯定没看懂，但你们也不用纠结，如果工作中你们真的遇到这样的需求，可以用如下方式实现

用编程语言，在内存中实现过滤嘛
把提需求的人干掉（个人不太推荐）

嵌套 EXISTS

有三张表

sql复制代码DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `Sno` char(9) DEFAULT NULL,
  `Sname` char(20) DEFAULT NULL,
  `Ssex` char(2) DEFAULT NULL,
  `Sage` int(11) DEFAULT NULL,
  `Sdept` char(20) DEFAULT NULL,
  UNIQUE KEY `Sno` (`Sno`)
) ENGINE=InnoDB;
INSERT INTO `student` VALUES ('200215121', '李勇', '男', '20', 'CS');
INSERT INTO `student` VALUES ('200215122', '刘晨', '女', '19', 'CS');
INSERT INTO `student` VALUES ('200215123', '王敏', '女', '18', 'MA');
INSERT INTO `student` VALUES ('200215124', '张立', '男', '19', 'IS');

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `Cno` char(4) NOT NULL,
  `Cname` char(40) DEFAULT NULL,
  `Cpno` char(4) DEFAULT NULL,
  `Ccredit` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`Cno`)
) ENGINE=InnoDB;
INSERT INTO `course` VALUES ('1', '数据库', '5', '4');
INSERT INTO `course` VALUES ('2', '数学', '', '2');
INSERT INTO `course` VALUES ('3', '信息系统', '1', '4');
INSERT INTO `course` VALUES ('4', '操作系统', '6', '3');
INSERT INTO `course` VALUES ('5', '数据结构', '7', '4');
INSERT INTO `course` VALUES ('6', '数据处理', '', '2');
INSERT INTO `course` VALUES ('7', 'PaSCal语言', '6', '4');

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `Sno` char(9) DEFAULT NULL,
  `Cno` char(4) DEFAULT NULL,
  `Grade` smallint(6) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO `sc` VALUES ('200215121', '1', '92');
INSERT INTO `sc` VALUES ('200215121', '2', '85');
INSERT INTO `sc` VALUES ('200215121', '3', '88');
INSERT INTO `sc` VALUES ('200215122', '2', '90');
INSERT INTO `sc` VALUES ('200215122', '3', '80');

如下 SQL 是查什么？

sql复制代码SELECT Sname
FROM student
WHERE NOT EXISTS
(
	SELECT * FROM course
	WHERE NOT EXISTS
	(
		SELECT * FROM sc
		WHERE sc.Sno = student.Sno
		AND sc.Cno = course.Cno
	)
);

另外，如下两个需求，SQL 该怎么写

查询被所有学生选修的课程的课名
查询选修了 200215122 学生选修的全部课程的学生学号

感兴趣的可以试试，答案在：SQL 中的 EXISTS 到底做了什么？或者《数据库系统概论（第4版）》

学吧，学无止尽.png

总结

SQL 中的谓词分两种：一阶谓词和二阶谓词（EXISTS），区别主要在于接收的参数不同，一阶谓词接收的是 行，而二阶谓词接收的是 行的集合
SQL 中没有与 全称量词 相当的谓词，需要进行双重否定转换，然后用 NOT EXISTS 实现
EXISTS 之所以难用（不是不好用，而是不会用），主要是 全称量词 的命题转换（肯定 ⇔ 双重否定）比较难，实际工作中往往会舍弃 EXISTS，寻找它的替代方式，可能是 SQL 的替代，也可能是业务方面的转换，所以说，EXISTS 掌握不了没关系，但是能掌握那是最好了

参考

《SQL基础教程》

《SQL进阶教程

本文转载自: 掘金

开发者博客 – 和开发相关的这里全都有