集合运算

# 190.集合运算

之前我们学习的 SQL 都是仅仅针对一张表的查询，接下来我们开始学习 2 张及以上的表的 SQL 语句。

‍ ‍

# 什么是集合运算

集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

‍

# union：并集

我们新建一个表（结构和 Product 一样）进行测试：

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


-- 插入数据：
INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

‍

注意编号为“0009 ”的“手套”和“0010 ”的“水壶”是 Product 表中没有的商品。

‍

接下来我们开始进行并集：

SELECT product_id, product_name
  FROM Product
UNION
SELECT product_id, product_name
  FROM Product2;

--结果：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤衫        |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 擦菜板       |
| 0008       | 圆珠笔       |
| 0009       | 手套         |
| 0010       | 水壶         |
+------------+--------------+
10 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

‍

上述结果包含了两张表中的全部商品。这就是我们在学校学过的集合中的并集运算，文氏图：

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

‍

# union all：包含重复行的集合运算

接下来给大家介绍在 UNION 的结果中保留重复行的语法。其实非常简单，只需要在 UNION 后面添加 ALL 关键字就可以了。这里的 ALL 选项，在 UNION 之外的集合运算符中同样可以使用

SELECT product_id, product_name
  FROM Product
UNION ALL
SELECT product_id, product_name
  FROM Product2;

-- 结果：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤衫        |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 擦菜板       |
| 0008       | 圆珠笔       |
| 0001       | T恤衫        |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0009       | 手套         |
| 0010       | 水壶         |
+------------+--------------+
13 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

可以看到有重复的记录（T 恤，打孔器，运动 T 恤）

‍

# intersect：交集

intersect 用于选取两个记录集合中的公共部分：

SELECT product_id, product_name
  FROM Product
INTERSECT
SELECT product_id, product_name
  FROM Product2
ORDER BY product_id;


-- 结果：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤衫        |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
+------------+--------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

‍

结果中只包含两张表中记录的公共部分，文氏图如下

‍

# except：差集

示例：


SELECT product_id, product_name
  FROM Product
EXCEPT
SELECT product_id, product_name
  FROM Product2
ORDER BY product_id;

-- 结果：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 擦菜板       |
| 0008       | 圆珠笔       |
+------------+--------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

‍

结果中只包含 Product 表中记录除去 Product2 表中记录之后的剩余部分，文氏图：

‍

注意：Oracle 中使用的是 MINUS，而不是 except

‍

# 集合运算注意事项

注意事项 ① ——作为运算对象的记录的列数必须相同

例如，像下面这样，一部分记录包含 2 列，另一部分记录包含 3 列时会发生错误，无法进行加法运算。

-- 列数不一致时会发生错误
SELECT product_id, product_name
  FROM Product
UNION
SELECT product_id, product_name, sale_price
  FROM Product2;

1
2
3
4
5
6

‍

注意事项 ②——作为运算对象的记录中列的类型必须一致

从左侧开始，相同位置上的列必须是同一数据类型。例如下面的 SQL 语句，虽然列数相同，但是第 2 列的数据类型并不一致（一个是数值类型，一个是日期类型），因此会发生错误。

-- 数据类型不一致时会发生错误
SELECT product_id, sale_price
  FROM Product
UNION
SELECT product_id, regist_date
  FROM Product2;

1
2
3
4
5
6

一定要使用不同数据类型的列时，可以使用 6-1 节中的类型转换函数 CAST 。

‍

注意事项 ③——可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次

通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句，之前学过的 WHERE 、GROUP BY 、HAVING 等子句都可以使用。但是 ORDER BY 只能在最后使用一次：

SELECT product_id, product_name
  FROM Product
 WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
  FROM Product2
 WHERE product_type = '厨房用具'
ORDER BY product_id;

1
2
3
4
5
6
7
8

‍

# 小结

中我们学习了以下 3 个集合运算符：

UNION （并集）
EXCEPT （差集）
INTERSECT （交集）

并集可以理解为加法，而交集可以理解为减法，那集合运算有无乘法、除法呢？

也是有的，下篇文章就是介绍联结运算的，最后讲的内容就是乘法；

而除法比较复杂，并且目前也没引入 SQL 标准，实现起来比较麻烦，这里暂且不表。

提示：除法可以用减法来实现，例如 6 / 2 = 3，那么 6 减去 2，减 3 次结果为 0，那么商就是 3

‍

（完）

‍

上次更新: 2024/12/4 21:15:24

← CASE 表达式联结查询-JOIN→