目录

• 一、什么是集合运算
• 二、表的加法——UNION
• 三、集合运算的注意事项
• 四、包含重复行的集合运算——ALL 选项
• 五、选取表中公共部分——INTERSECT
• 六、记录的减法——EXCEPT

本文将会和大家一起学习集合运算操作。集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

本文重点

集合运算就是对满足同一规则的记录进行的加减等四则运算。

使用 UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运算符来进行集合运算。

集合运算符可以去除重复行。

如果希望集合运算符保留重复性，就需要使用 ALL 选项。

一、什么是集合运算

截至目前，我们已经学习了 从表中读取数据 以及 插入数据 的方法。

所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。

通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。

像这样用来进行集合运算的运算符称为集合运算符。

本文将会为大家介绍表的加减法，SQL 如何使用内联结、外联结和交叉联结 将会和大家一起学习进行“表联结”的集合运算符及其使用方法-->。

二、表的加法——UNION

首先为大家介绍的集合运算符是进行记录加法运算的 UNION（并集）。

在学习具体的使用方法之前，我们首先添加一张表，该表的结构与之前我们使用的 Product（商品）表相同，只是表名变为 Product2（商品 2）（代码清单 1）。

代码清单 1 创建表 Product2（商品 2）

CREATE TABLE Product2(product_id     CHAR(4)      NOT NULL, product_name   VARCHAR(100) NOT NULL, product_type   VARCHAR(32)  NOT NULL, sale_price     INTEGER      , purchase_price INTEGER      , regist_date    DATE         , PRIMARY KEY (product_id));

接下来，我们将代码清单 2 中的 5 条记录插入到 Product2 表中。

商品编号（product_id）为“0001”~“0003”的商品与之前 Product 表中的商品相同，而编号为“0009”的“手套”和“0010”的“水壶”是 Product 表中没有的商品。

代码清单 2 将数据插入到表 Product2（商品 2）中

SQL Server PostgreSQL

BEGIN TRANSACTION; ---------①INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2009-09-20');INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');COMMIT;

特定的 SQL

不同的 DBMS 的事务处理的语法也不尽相同。代码清单 2 中的 DML 语句在 MySQL 中执行时，需要将 ① 部分更改为“START TRANSACTION;”。在 Oracle 和 DB2 中执行时，无需用到 ① 的部分（请删除）。

详细内容请大家参考 什么是 SQL 事务 中的“创建事务”。

这样我们的准备工作就完成了。接下来，就让我们对上述两张表进行“Product 表 + Product2 表”的加法计算吧。语法请参考代码清单 3。

代码清单 3 使用 UNION 对表进行加法运算

SELECT product_id, product_name  FROM ProductUNIONSELECT product_id, product_name  FROM Product2;

执行结果：

product_id | product_name-----------+------------- 0001      | T恤衫 0002      | 打孔器 0003      | 运动T恤 0004      | 菜刀 0005      | 高压锅 0006      | 叉子 0007      | 擦菜板 0008      | 圆珠笔 0009      | 手套 0010      | 水壶

上述结果包含了两张表中的全部商品。可能有些读者会发现，这就是我们在学校学过的集合中的并集运算，通过文氏图会看得更清晰（图 1）。

图 1　使用 UNION 对表进行加法（并集）运算的图示

商品编号为“0001”~“0003”的 3 条记录在两个表中都存在，因此大家可能会认为结果中会出现重复的记录，但是 UNION 等集合运算符通常都会除去重复的记录。

法则 1

集合运算符会除去重复的记录。

三、集合运算的注意事项

其实结果中也可以包含重复的记录，在介绍该方法之前，还是让我们先来学习一下使用集合运算符时的注意事项吧。不仅限于 UNION，之后将要学习的所有运算符都要遵守这些注意事项。

• 注意事项 ① ——作为运算对象的记录的列数必须相同
• 例如，像下面这样，一部分记录包含 2 列，另一部分记录包含 3 列时会发生错误，无法进行加法运算。
• -- 列数不一致时会发生错误SELECT product_id, product_nameFROM ProductUNIONSELECT product_id, product_name, sale_priceFROM Product2;
• 注意事项 ②——作为运算对象的记录中列的类型必须一致
• 从左侧开始，相同位置上的列必须是同一数据类型。
• 例如下面的 SQL 语句，虽然列数相同，但是第 2 列的数据类型并不一致（一个是数值类型，一个是日期类型），因此会发生错误 [1]。
• -- 数据类型不一致时会发生错误SELECT product_id, sale_priceFROM ProductUNIONSELECT product_id, regist_dateFROM Product2;
• 一定要使用不同数据类型的列时，可以使用 SQL 常用的函数 中的类型转换函数 CAST。
• 注意事项 ③——可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次
• 通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句，之前学过的 WHERE、GROUP BY、HAVING 等子句都可以使用。
• 但是 ORDER BY 只能在最后使用一次（代码清单 4）。
• 代码清单 4 ORDER BY 子句只在最后使用一次
• SELECT product_id, product_nameFROM ProductWHERE product_type = '厨房用具'UNIONSELECT product_id, product_nameFROM Product2WHERE product_type = '厨房用具'ORDER BY product_id;
• 执行结果：
• product_id | product_name-----------+--------------0004 | 菜刀0005 | 高压锅0006 | 叉子0007 | 擦菜板0010 | 水壶

四、包含重复行的集合运算——ALL 选项

接下来给大家介绍在 UNION 的结果中保留重复行的语法。其实非常简单，只需要在 UNION 后面添加 ALL 关键字就可以了。

这里的 ALL 选项，在 UNION 之外的集合运算符中同样可以使用（代码清单 5）。

代码清单 5 保留重复行

SELECT product_id, product_name  FROM ProductUNION ALLSELECT product_id, product_name  FROM Product2;

执行结果：

法则 2

在集合运算符中使用 ALL 选项，可以保留重复行。

五、选取表中公共部分——INTERSECT

下面将要介绍的集合运算符在数的四则运算中并不存在，不过也不难理解，那就是选取两个记录集合中公共部分的 INTERSECT（交集） [2]。

让我们赶快来看一下吧。其语法和 UNION 完全一样（代码清单 6）。

代码清单 6 使用 INTERSECT 选取出表中公共部分

Oracle SQL Server DB2 PostgreSQL

SELECT product_id, product_name  FROM ProductINTERSECTSELECT product_id, product_name  FROM Product2ORDER BY product_id;

执行结果：

 product_id | product_name------------+-------------- 0001       | T恤衫 0002       | 打孔器 0003       | 运动T恤

大家可以看到，结果中只包含两张表中记录的公共部分。该运算的文氏图如下所示（图 2）。

图 2　使用 INTERSECT 选取出表中公共部分的图示

与使用 AND 可以选取出一张表中满足多个条件的公共部分不同，INTERSECT 应用于两张表，选取出它们当中的公共记录。

其注意事项与 UNION 相同，我们在“集合运算的注意事项”和“保留重复行的集合运算”中已经介绍过了。希望保留重复行时同样需要使用 INTERSECT ALL。

六、记录的减法——EXCEPT

最后要给大家介绍的集合运算符就是进行减法运算的 EXCEPT（差集）[3]，其语法也与UNION 相同（代码清单 7）。

代码清单 7 使用 EXCEPT 对记录进行减法运算

SQL Server DB2 PostgreSQL

SELECT product_id, product_name  FROM ProductEXCEPTSELECT product_id, product_name  FROM Product2ORDER BY product_id;

特定的 SQL

在 Oracle 中执行代码清单 7 或者代码清单 8 中的 SQL 时，请将 EXCEPT 改为 MINUS。

-- Oracle中使用MINUS而不是EXCEPT SELECT … FROM … MINUS SELECT … FROM …;

执行结果：

product_id | product_name-----------+-------------- 0004      | 菜刀 0005      | 高压锅 0006      | 叉子 0007      | 擦菜板 0008      | 圆珠笔

大家可以看到，结果中只包含 Product 表中记录除去 Product2 表中记录之后的剩余部分。该运算的文氏图如图 3 所示。

图 3　使用 EXCEPT 对记录进行减法运算的图示

EXCEPT 有一点与 UNION 和 INTERSECT 不同，需要注意一下。

那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。4 + 2 和 2 + 4 的结果相同，但是 4 - 2 和 2 - 4 的结果却不一样。

因此，我们将之前 SQL 中的 Product 和 Product2 互换，就能得到代码清单 8 中的结果。

代码清单 8 被减数和减数位置不同，得到的结果也不同

SQL Server DB2 PostgreSQL

-- 从Product2的记录中除去Product中的记录SELECT product_id, product_name  FROM Product2EXCEPTSELECT product_id, product_name  FROM ProductORDER BY product_id;

执行结果：

 product_id | product_name------------+-------------- 0009       | 手套 0010       | 水壶

上述运算的文氏图如图 4 所示。

图 4　使用 EXCEPT 对记录进行减法运算的图示（从 Product2 中除去 Product 中的记录）

到此，对 SQL 提供的集合运算符的学习已经结束了。

可能有些读者会想“唉？怎么没有乘法和除法呢？”关于乘法的相关内容，我们将在 SQL 如何使用内联结、外联结和交叉联结 详细介绍。

此外，SQL 中虽然也存在除法，但由于除法是比较难理解的运算，属于中级内容，因此我们会在 SQL 如何使用内联结、外联结和交叉联结 末尾的专栏中进行一些简单的介绍，感兴趣的读者请参考专栏“关系除法”。

1. 实际上，在有些 DBMS 中，即使数据类型不同，也可以通过隐式类型转换来完成操作。但由于并非所有的 DBMS 都支持这样的用法，因此还是希望大家能够使用恰当的数据类型来进行运算。 ??
2. 因为 MySQL 尚不支持 INTERSECT，所以无法使用。 ??
3. 只有 Oracle 不使用 EXCEPT，而是使用其特有的 MINUS 运算符。使用 Oracle 的用户，请用 MINUS 代替 EXCEPT。此外，MySQL 还不支持 EXCEPT，因此也无法使用。 ??