SQL进阶——集合运算

原创于 2026-07-06 15:30:32 发布 · 18 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

SQL经典实例

集合运算

SQL语言的基础之一是集合论。但是，在很长一段时间内，由于SQL没能很好地支持集合运算，所以相关功能并没有被人们充分地利用。过去这些年，SQL凑齐了大部分基础的集合运算，人们终于可以真正地使用它了。

1、集合运算的几个注意事项

1.1、SQL能操作具有重复行的集合，可以通过可选项ALL来支持

一般的集合论是不允许集合里存在重复元素的，因此集合{1, 1, 2, 3, 3, 3}和集合{1, 2, 3}被视为相同的集合。但是关系数据库里的表允许存在重复的行，所以它也被称为多重集合（multiset，亦称bag）。

因此，SQL的集合运算符也提供了允许重复和不允许重复的两种用法。如果直接使用UNION或INTERSECT，结果里就不会出现重复的行。如果想在结果里留下重复行，则可以加上可选项ALL，写作UNION ALL。ALL的作用和SELECT子句里的DISTINCT可选项刚好相反。但是，不知道为什么，SQL并不支持UNION DISTINCT这样的写法。

除了运算结果以外，这两种用法还有一个不同点：集合运算符为了排除掉重复行，会默认发生排序，而加上可选项ALL之后，就不会再排序了，所以性能会提升。这是用于优化查询性能的非常有效的方法，所以如果不关心结果是否存在重复行，或者确定结果里不会产生重复行，那么加上可选项ALL会更好一些（不过，在行数很少的情况下其实不会有什么差别）。

1.2、集合运算符有优先级

标准SQL规定，INTERSECT比UNION和EXCEPT的优先级更高。因此，当同时使用UNION和INTERSECT，又想让UNION优先执行时，必须用括号明确地指定运算顺序。

1.3、各个DBMS供应商在集合运算的实现程度上参差不齐

前面说过，早期的SQL对集合运算的支持程度不是很高。受此影响，各个DBMS供应商的实现程度也参差不齐。SQL Server从2005版开始支持INTERSECT和EXCEPT，而MySQL直到2018年还都不支持。还有一些DBMS像Oracle这样，实现了EXCEPT功能但却将其命名为MINUS。这一点比较麻烦，因为Oracle用户需要在使用时将EXCEPT全部改写成MINUS。

1.4、除法运算没有标准定义

四则运算里的和(UNION)、差(EXCEPT)、积(CROSSJOIN)都被引入标准SQL了，但是很遗憾，商(DIVIDE BY) 因为各种原因迟迟没能标准化（具体原因请参考1-6节里的解释）。因此，现阶段我们需要自己写SQL语句来实现除法运算。

2、比较表和表：检查集合相等性之基础篇

在迁移数据库的时候，或者需要比较备份数据和最新数据的时候，我们需要调查两张表是否是相等的。这里说的“相等”指的是行数和列数以及内容都相同，即“是同一个集合”的意思。例如，下面的表tbl_A和表tbl_B，虽然名字不同，但它们是同一个集合。

在这里插入图片描述

/* 比较表和表：检查集合相等性 */
CREATE TABLE Tbl_A (
	keycol CHAR(1) PRIMARY KEY,
  col_1 INTEGER , 
  col_2 INTEGER, 
  col_3 INTEGER
);

CREATE TABLE Tbl_B (
	keycol CHAR(1) PRIMARY KEY,
  col_1 INTEGER, 
  col_2 INTEGER, 
  col_3 INTEGER
);

/* 表相等的情况 */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', 2, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', 5, 1, 6);

DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', 2, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', 5, 1, 6);

有没有什么办法，能让我们像比较文件一样来比较两张表是相等还是不相等呢？如果像上面这两张表一样，只有几行数据，那么用眼睛就能看清楚。但是，如果数据有几百列或者几千万行，就不可能用眼睛看清楚了。

此时，解决问题的方法有两种。我们先来看一个简单的，只用UNION就能实现的方法。这里，我们先假设已经事先确认了表tbl_A和表tbl_B的行数是一样的（如果行数不一样，那就不需要比较其他的了）。

这两张表的行数都是3。如果下面这条SQL语句的执行结果是3，则说明两张表是相等的；如果执行结果大于3，则说明两张表不相等。

如果这个查询的执行结果与tbl_A及tbl_B的行数一致，则两张表是相等的

SELECT COUNT(*)AS row_cnt
FROM ( 
	SELECT *
	FROM Tbl_A
	UNION
	SELECT *
	FROM Tbl_B 
) TMP;

+---------+
| row_cnt |
+---------+
|       3 |
+---------+

这个方法的原理是什么呢？请回忆一下“导入篇”里的注意事项1。如果集合运算符里不加上可选项ALL，那么重复行就会被排除掉。因此，如果表tbl_A和表tbl_B是相等的，那么排除掉重复行后，两个集合是完全重合的。
在这里插入图片描述
如果像下面这样，两张表有一行数据不一样，执行结果就会变成4。这是因为，如果某一行的数据不同，那么排除掉重复行后，这一行仍然会存在，两张表没办法完全重合。

前面的SQL语句可以用于包含NULL数据的表，而且不需要指定列数、列名和数据类型等就能使用，还是很方便的。此外，因为这条SQL语句只使用了UNION，所以在MySQL数据库里也可以使用。当然，我们也可以只比较表里的一部分列或者一部分行，只需要指定想要比较的列的名称，或者在WHERE子句里加入过滤条件就可以了。

我们可以从上面的例子看出，对于任意的表S，都有下面的公式成立。

S UNION S = S

这是UNION的一个非常重要的性质，在数学上我们称之为幂等性(idempotency)。幂等性原本是群论等抽象代数里的概念，有多种含义，与上例在意思上相近的一种是“二目运算符对任意S，都有SS=S成立”。如果按这个意思理解，UNION运算是幂等的。

对于集合运算里的UNION，如果将S UNION S看作一个执行单元，那么因为无论执行多少次结果都是相同的，所以S UNION S也是幂等的。由此，我们可以把它用在比较三张以上的表是否相等的情况。

同一个集合无论加多少次，结果都是相同的

S UNION S UNION S UNION S … UNION S = S

有一点需要注意的是，如果改成对S执行多次UNION ALL操作，那么每次结果都会有变化，所以说UNION ALL不具有幂等性。类似地，如果对拥有重复行的表进行UNION操作，也会失去幂等性。换句话说，UNION的这个优雅而强大的幂等性只适用于数学意义上的集合，对SQL中有重复数据的多重集合是不适用的。由此，我们应该明白主键对于表来说是多么重要。

3、比较表和表：检查集合相等性之进阶篇

在前面的解法中，在比较两张表之前，我们需要先要查看两张表里的数据的行数。虽然这点准备工作也不算麻烦，但我们还是来改进一下这条SQL语句，让它能直接比较两张表吧。这里还是使用表tbl_A和表tbl_B来演示。

在集合论里，判定两个集合是否相等时，一般使用下面这2种方法。

在这里插入图片描述

第1种方法利用了两个集合的包含关系来判定其相等性，意思是“如果集合 A包含集合 B，且集合 B包含集合 A，则集合 A和集合4相等”。这个办法可行，只是有点麻烦。

第2种方法利用了两个集合的并集和交集来判定其相等性。如果用SQL语言描述，那就是“如果A UNION B=AINTERSECT B，则集合 A和集合 B相等”。这种方法写起来更简单。

如果集合 A和集合 B相等，那么A UNION B=A=B以及AINTERSECT B=A=B都是成立的。没错，除了UNION之外，另一个具有幂等性的运算符就是INTERSECT。

相反，如果 A ≠ B，UNION和INTERSECT的结果就不相同了。UNION的执行结果的行数肯定会变多。图1.9.2描述了两个不相同的集合 A和 B之间的差异逐渐变小、相互接近的过程，请大家想象一下这个过程，这样应该更好理解UNION和INTERSECT之间的区别。

在这里插入图片描述
剩下的问题是，对 A和 B分别进行UNION运算和INTERSECT运算后，如何比较这两个的结果。目前，我们已经明白了下面这一点。

( A INTERSECT B )⊆ ( A UNION B )

因此，只需要判定 (A UNION B)EXCEPT (AINTERSECT B)的结果集是不是空集就可以了。如果A=B，则这个结果集是空集，否则，这个结果集里肯定有数据。

-- 两张表相等时返回“相等”，否则返回“不相等”
SELECT CASE WHEN COUNT(*) = 0
            THEN '相等'
            ELSE '不相等' END AS result
FROM (
	(
		SELECT * FROM Tbl_A
		UNION
		SELECT * FROM Tbl_B
	)
	EXCEPT
	(
		SELECT * FROM Tbl_A
		INTERSECT
		SELECT * FROM Tbl_B
	)
) TMP;

这条SQL语句与“导入篇”的那条SQL语句具有同样的优点，即不需要指定列名和列数，还可以用于包含NULL的表，而且这个改进版连事先查询两张表的行数这种准备工作也不需要做了。但是，虽然功能改进了，却也带来了一些缺陷。由于这里需要进行4次排序（3次集合运算加上1次DISTINCT），所以性能会有所下降（不过这条SQL语句也不需要频繁执行，所以这点缺陷也不是不能容忍的）。此外，因为这里使用了INTERSECT和EXCEPT，所以这条SQL语句目前不能在MySQL里执行。请大家综合考虑二者的优势和缺陷，再做出使用哪条SQL语句的选择。

那么，既然我们知道了两张表的数据有差异，接下来就把不同的行输出来看一看吧。diff命令是用来比较文件的，而这里的SQL语句就相当于diff，只不过是用来比较表的。我们只需要求出两个集合的异或集就可以了，代码如下所示。

-- 用于比较表与表的 diff
(SELECT * FROM  tbl_A
 EXCEPT
 SELECT * FROM  tbl_B)
UNION ALL
(SELECT * FROM  tbl_B
 EXCEPT
 SELECT * FROM  tbl_A);

key   col_1   col_2   col_3
---   -----   -----   -----
B         0       7       9
B         0       7       8

因为 A-B和 B-A之间不可能有交集，所以在合并这两个结果时使用UNION ALL也没有关系。在 A和 B一方包含另一方时,这条SQL语句也是成立的（这时 A-B和 B-A中有一个会是空集）。需要注意的是，在SQL中，括号决定了运算的先后顺序，它非常重要，如果去掉括号，结果就会不正确。

4、用差集实现关系除法运算

SQL里还没有能直接进行关系除法运算的运算符。要想进行除法运算，必须自己去实现。实现方法比较多，其中具有代表性的是下面这3种。

嵌套使用NOT EXISTS。
使用HAVING子句转换成一对一关系。
把除法变成减法。

这里将介绍一下第3种方法。

关于示例数据，我们选用的是下页这两张员工技术信息管理表。

在这里插入图片描述

这里的问题是，从表EmpSkills中找出精通表Skills中所有技术的员工。也就是说，答案是相田和神崎。平井很可惜，会的技术很多，但是不会Java，所以他落选了。

这里要介绍的方法的思路跟面向过程语言非常像，所以可能比使用HAVING子句的方法更好理解一些。那么，我们先来看一下答案吧。

-- 通过差集进行关系除法运算（有余数）
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS (
	SELECT skill
	FROM Skills
	EXCEPT
	SELECT skill
	FROM EmpSkills ES2
	WHERE ES1.emp = ES2.emp
);

emp
---
相田
神崎

这个方法的要点在于EXCEPT运算符和关联子查询。关联子查询是建立在表EmpSkills上的，因为我们要针对每个员工进行集合运算。具体来说，就是从需求的技术的集合中减去每个员工自己的技术的集合，如果结果是空集，则说明该员工具备所有的需求的技术，否则说明该员工不具备某些需求的技术。

我们以相田为例来看一下。从图可以看到，集合运算的结果是空集，所以他符合条件。
在这里插入图片描述
下面再看一下平井的情况，如图所示。

在这里插入图片描述
结果里剩下了Java这一行，所以平井不符合条件。也就是说，这里的解题思路是先把处理的单位分割成以员工为单位，然后将除法运算还原成更加简单的减法运算。这个解法与“将困难分解”那句格言的思路一致，还是很巧妙的。

下面再看一下解题过程吧。大家有没有想到些什么呢？没错，其实这条SQL语句的处理方法与面向过程语言里的中断控制处理很像。请试着想象一下把这两张表当成两个文件，然后一行一行循环处理的过程。针对某一个员工循环判断其对各种技术的掌握情况，如果存在企业需求的技术，就进行减法运算；如果不存在就终止该员工的循环，继续对下一个员工执行同样的处理。

前面之所以说这种解法可能更好理解，也是因为它容易让人联想到循环——“原来如此。本来是除法(division)，但这里将问题分割(divide)了一下，就可以用减法来解答了啊。”

5、寻找相等的子集

下面这个谜题是戴特于1993年提出的，一直非常有名。这里也沿用他当时使用的表示“供应商-零件”关系的表作为示例数据，即一张展示了各个供应商及其经营的零件的表。

在这里插入图片描述

CREATE TABLE SupParts (
	sup CHAR(32) NOT NULL,
	part CHAR(32) NOT NULL,
	PRIMARY KEY(sup, part)
);

INSERT INTO SupParts VALUES('A',  '螺丝');
INSERT INTO SupParts VALUES('A',  '螺母');
INSERT INTO SupParts VALUES('A',  '管子');
INSERT INTO SupParts VALUES('B',  '螺丝');
INSERT INTO SupParts VALUES('B',  '管子');
INSERT INTO SupParts VALUES('C',  '螺丝');
INSERT INTO SupParts VALUES('C',  '螺母');
INSERT INTO SupParts VALUES('C',  '管子');
INSERT INTO SupParts VALUES('D',  '螺丝');
INSERT INTO SupParts VALUES('D',  '管子');
INSERT INTO SupParts VALUES('E',  '保险丝');
INSERT INTO SupParts VALUES('E',  '螺母');
INSERT INTO SupParts VALUES('E',  '管子');
INSERT INTO SupParts VALUES('F',  '保险丝');

我们需要求的是，经营的零件在种类数和种类上都完全相同的供应商组合。由上面的表格我们可以看出，答案是A-C和B-D这两组。A和E虽然经营的零件的种类数都是3，但是零件的种类却不完全相同，所以不符合要求。F则在种类数和种类上跟其他供应商都不相同，所以也不考虑。

这个问题看起来很简单，为什么会成为谜题呢？原因是，SQL并没有提供任何用于检查集合包含关系或相等性的谓词。IN谓词只能用来检查元素是否属于某个集合(∈)，而不能检查集合是否是某个集合的子集。据说，IBM研制的第一个关系数据库实验系统——System R曾经实现了用CONTAINS这一谓词来检查集合间的包含关系，但是后来因为性能方面的问题，它被删除了，直到现在也没有恢复。所以，我们不妨称它为“传说中的谓词”。

这个问题的特点在于比较的对象是集合。这一点与前面的关系除法运算很相似。但是，在关系除法运算中，比较对象的一方是固定的（例如前面例题里的表Skills），而这次比较的双方都不固定。这时，我们需要比较所有子集的全部组合，所以这个问题更具有普遍性。

首先，我们来生成供应商的全部组合。方法是大家已经非常习惯了的非等值连接。使用聚合只是为了去除重复。

-- 生成供应商的全部组合
SELECT SP1.sup AS s1, SP2.sup AS s2
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup < SP2.sup
GROUP BY SP1.sup, SP2.sup;

+----+----+
| s1 | s2 |
+----+----+
| A  | B  |
| A  | C  |
| A  | D  |
| A  | E  |
| A  | F  |
| B  | C  |
| B  | D  |
| B  | E  |
| B  | F  |
| C  | D  |
| C  | E  |
| C  | F  |
| D  | E  |
| D  | F  |
| E  | F  |
+----+----+

接下来，我们检查一下这些供应组合是否满足公式“(A 属于 B)且(B 属于 A)=>(A = B)”。这个公式等价于下面2个条件。

两个供应商都经营相同种类的零件
两个供应商经营的零件的种类数相同（即存在一一映射）

条件1只需要简单地按照“零件”列进行连接，而条件2需要用COUNT函数来描述。

SELECT SP1.sup AS s1, SP2.sup AS s2
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup < SP2.sup                 -- 生成供应商的全部组合
AND SP1.part = SP2.part               -- 条件 1 ：经营相同种类的零件
GROUP BY SP1.sup, SP2.sup
HAVING COUNT(*) = (
	SELECT COUNT(*)      -- 条件 2 ：经营的零件的种类数相同
	FROM SupParts SP3
	WHERE SP3.sup = SP1.sup
) AND COUNT(*)= (
	SELECT COUNT(*)
	FROM SupParts SP4
	WHERE SP4.sup = SP2.sup
);

+----+----+
| s1 | s2 |
+----+----+
| A  | C  |
| B  | D  |
+----+----+

大家把HAVING子句里的两个条件当成“精确关系除法运算”，也许会更容易理解。加上这两个条件后，我们就能保证集合 A和集合 B的元素个数一致，不会出现不足或者过剩（即存在一一映射）。而且，条件1又保证了经营的零件的种类也都是完全相同的。这样就满足了本题的全部条件。

对于这个谜题，人们提出了各种各样的解法。本例介绍的方法对关系除法运算进行了一般化，充分运用了SQL的面向集合的特性，是一种比较巧妙的解法。这种解法告诉我们，SQL在比较两个集合时，并不是以行为单位来比较的，而是把集合当作整体来处理的。

最后稍微说点题外话。刚才我们把CONTAINS谓词称作“传说中的谓词”，但是如果它可以使用，我们就可以像下面这样写。

SELECT 'A CONTAINS B'
FROM SupParts
WHERE (
	SELECT part
	FROM SupParts
	WHERE sup = 'A'
) CONTAINS (
	SELECT part
	FROM SupParts
	WHERE sup = 'B'
);

这里解释一下，[A 属于 B]即“供应商B经营的所有商品，供应商A也都经营”。如果存在这样的供应商组合，则返回ACONTAINS B这个字符串。怎么样，如果CONTAINS真的能用，是不是很方便呢？凭借现在的技术，性能方面也许不再是问题了，所以或许不久以后这个功能就可以复活了呢。

6、用于删除重复行的高效SQL

最后，我们通过关于“删除重复行”的例题来练习一下如何应用集合运算。我们再看一下当时用过的那张没有主键的恐怖的表。

在这里插入图片描述

CREATE TABLE Products	(
	rowid INTEGER,
	name CHAR(16),
	price INTEGER
);

INSERT INTO Products VALUES(1, '苹果',  50);
INSERT INTO Products VALUES(2, '橘子', 100);
INSERT INTO Products VALUES(3, '橘子', 100);
INSERT INTO Products VALUES(4, '橘子', 100);
INSERT INTO Products VALUES(5, '香蕉',  80);

当时介绍的解法是使用关联子查询，代码非常简单。

-- 删除重复行：使用关联子查询
DELETE FROM Products
WHERE rowid < ( 
	SELECT MAX(P2.rowid)
	FROM Products P2
	WHERE Products.name  = P2. name
	AND Products.price = P2.price 
);

这种做法不算太差，只是关联子查询的性能问题是一个难点（光是DELETE处理就比较耗时了）。因此，这里我们思考一下如何不用关联子查询也能实现同样的功能。

使用关联子查询时的思路是按照{商品名，价格 }的组合汇总后，求出每个组合的最大rowid，然后把其余的行都删除。直接求删除哪些行比较困难，所以这里是先求出要留下的行，然后将其从全部组合中提取出来，最后把剩下的行删除——也就是补集的思路。这种使用关联子查询的方法是以{商品名，价格 }的组合为单位来处理的（相当于面向过程语言里的中断控制处理）。现在，我们要做的则是在子查询里直接求出要删除的rowid。

假设表中已经像下面这样加上了rowid列。
在这里插入图片描述
使用极值函数让每组只留下一个rowid——这一点与之前的做法一样。不同的是，这次我们需要把要留下的集合从表Products这个集合中减掉。SQL语句如下所示。

-- 用于删除重复行的高效 SQL 语句(1)：通过 EXCEPT 求补集
DELETE FROM Products
WHERE rowid IN ( 
	SELECT rowid          -- 全部 rowid
	FROM Products
	EXCEPT                -- 减去
	SELECT MAX(rowid)    -- 要留下的 rowid
	FROM Products
	GROUP BY name, price
);

非相关的子查询的返回结果是常数列表“2, 3”。其中，使用EXCEPT求补集的逻辑如图所示。
在这里插入图片描述

使用EXCEPT后，我们就可以轻松求得补集了。这个方法想必大家已经非常明白了。此外，把EXCEPT改写成NOT IN也可以实现相同的结果。代码如下所示。

-- 删除重复行的高效 SQL 语句(2)：通过 NOT IN 求补集
DELETE FROM Products
WHERE rowid NOT IN ( 
	SELECT MAX(rowid)
	FROM Products
	GROUP BY name, price
);

这两种方法的性能优劣主要取决于表的规模，以及删除的行数与留下的行数之间的比例。不过，第二种方法有一个优点，那就是不支持EXCEPT的数据库也可以使用。

标签

#sql #数据库