「SQL 数据分析系列」6. 使用集合

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第六章使用集合

尽管每次与数据库交互的时候可以只处理一行数据，但是关系数据库实际上处理的都是集合。本章探讨如何使用各种集合操作符联合多个结果集。在简要介绍集合论之后，我将演示如何使用集合操作符 union、intersect 和 except 联合使用多个数据集。

集合理论入门

在世界上许多地方，基础集合理论都有包含在小学数学课程中。也许看到下图（6-1）能唤起你的一些回忆：

上图（6-1）中的阴影区域表示集合 A 和 B 的并集，这是两个集合的联合（重叠部分只包含一次）。是不是觉得很眼熟？是的话，那么你学过的知识现在就能派上用场了，如果觉得不熟悉也没关系，因为根据该图表也能很好地理解这个概念。

使用圆圈来表示两个数据集（A 和 B），想象两个集合共用的数据子集由图中所示的重叠部分所代表。由于集合论毫不关心没有重叠部分的数据集，所以下面我会使用同样的图表来说明集合操作。另一种集合操作只涉及两个数据集之间的重叠，被称为交集（intersection），如图（6-2）所示：

由集合 A 和 B 的交集操作生成的数据集只是两个集合之间的重叠部分。如果这两个集合没有重叠，则交集操作产生空集。

下图（6-3）所示的第三个也是最后一个集合操作称为差操作（except）。

下图（6-3）显示了 A except B 的结果，即完整的集合 A 中减去它与集合 B 重叠的部分。如果两个集合没有重叠，则运算 A except B 的结果是整个集合 A：

 使用这三种操作，或者联合使用不同的操作，可以产生所需的任何结果。例如，假设你想要构建一个如图（6-4）所示的集合：

该数据集包括集合 A 和 B 非重合区域的部分。仅用一种集合操作显然是无法产生该结果的，所以你先得构建一个包含集合 A 与 B 所有元素的数据集，再使用第二种操作移除重复部分。如果使用 A union B 描述并集，使用 A intersect B 描述交集，则可以使用下面的方法生成上图（6-4）所示的数据集：

(A union B) except (A intersect B)

当然，通常获得结果的方法不止一种，你也可以使用以下操作获得类似的结果：

(A except B) union (B except A)

使用图示是很容易理解这些概念的，下一节中会介绍如何在关系数据库中使用 SQL 集合操作符来实现它们。

集合理论实践

上一节的图示中用于表示数据集的圆并不代表数据集所包含的数据内容。然而，在处理实际数据时，如果要合并数据集，还需要了解数据集结构。例如，想象一下当你试图生成 customer 表和 city 表的并集时会发生的情况，这两个表的定义如下：

mysql> desc customer;

+-------------+----------------------+------+-----+-------------------+

| Field | Type | Null | Key | Default |

+-------------+----------------------+------+-----+-------------------+

| customer_id | smallint(5) unsigned | NO | PRI | NULL |

| store_id | tinyint(3) unsigned | NO | MUL | NULL |

| first_name | varchar(45) | NO | | NULL |

| last_name | varchar(45) | NO | MUL | NULL |

| email | varchar(50) | YES | | NULL |

| address_id | smallint(5) unsigned | NO | MUL | NULL |

| active | tinyint(1) | NO | | 1 |

| create_date | datetime | NO | | NULL |

| last_update | timestamp | YES | | CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+

mysql> desc city;

+-------------+----------------------+------+-----+-------------------+

| Field | Type | Null | Key | Default |

+-------------+----------------------+------+-----+-------------------+

| city_id | smallint(5) unsigned | NO | PRI | NULL |

| city | varchar(50) | NO | | NULL |

| country_id | smallint(5) unsigned | NO | MUL | NULL |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+

合并完这两个表之后，结果集中的第一列将包含 customer.customer_id 以及 city.city_id 列，第二列将是 customer.store_id 以及 city.city 列，诸如此类。其中有些列对是容易组合的（比如两个数字列），另外一些列是难以组合的，比如数字列与字符串列或字符串列与日期列。此外，合并表的第五列到第九列将只包含来自 customer 表的第五列到第九列，因为 city 表只有四列。显然，我们期望合并的两个数据集之间最好是有一些共性的。

因此，在对两个数据集执行集合操作时，必须遵循以下准则：

• 两个数据集的列数必须相同；

• 两个数据集的对应列的数据类型必须相同（或者服务器能够将其中一个数据类型转换为另一个类型）。

遵循这些规则，在实践中就能更容易理解“重叠数据”的含义了，对于两个集合中对应的数据列，它们需要有相同的字符串、数字或日期，才能被视为相同的行。

可以通过在两个 select 语句之间使用一个集合操作符来执行集合操作，如下所示：

mysql> SELECT 1 num, 'abc' str 

-> UNION 

-> SELECT 9 num, 'xyz' str;

+-----+-----+

| num | str |

+-----+-----+

| 1 | abc |

| 9 | xyz |

+-----+-----+

2 rows in set (0.02 sec)

每个单独的查询产生一个数据集，该数据集由一行组成，该行有一个数字列和一个字符串列。集合操作符（在本例中是 union）告诉数据库服务器合并两个集合中的所有行。因此，最终的集合将包括两个两列的行。此查询称为复合查询（compound query），因为它将多个独立的查询合并到一起。本书后面你会看到更加复杂的复合查询，会组合使用两个以上的查询来获得最终的查询结果。

集合操作符

SQL 语言包括三种集合操作符来实现本章前面描述的各种集合操作。另外，每个集合操作符有两种修饰符，一个包含重复项，一个去除重复项（但不一定是所有重复项）。下面各小节定义了每个操作符并演示了它们的使用过程。

union 操作符

union 和 union all 操作符允许组合多个数据集。两者的区别在于 union 对合并后的集合进行排序并删除重复项，而 union all 不这样做（会保留重复项）。使用 union all 时，最终数据集中的行数始终等于被合并的数据集的行数之和。这个操作是最容易执行的集合操作（从服务器的角度来看），因为服务器不需要检查冗余数据。以下示例演示如何使用 union all 操作符从多个表生成姓名的集合：

mysql> SELECT 'CUST' typ, c.first_name, c.last_name 

-> FROM customer c 

-> UNION ALL 

-> SELECT 'ACTR' typ, a.first_name, a.last_name 

-> FROM actor a;

+------+------------+-------------+

| typ | first_name | last_name |

+------+------------+-------------+

| CUST | MARY | SMITH |

| CUST | PATRICIA | JOHNSON |

| CUST | LINDA | WILLIAMS |

| CUST | BARBARA | JONES |

| CUST | ELIZABETH | BROWN |

| CUST | JENNIFER | DAVIS |

| CUST | MARIA | MILLER |

| CUST | SUSAN | WILSON |

| CUST | MARGARET | MOORE |

| CUST | DOROTHY | TAYLOR |

| CUST | LISA | ANDERSON |

| CUST | NANCY | THOMAS |

| CUST | KAREN | JACKSON |

...

| ACTR | BURT | TEMPLE |

| ACTR | MERYL | ALLEN |

| ACTR | JAYNE | SILVERSTONE |

| ACTR | BELA | WALKEN |

| ACTR | REESE | WEST |

| ACTR | MARY | KEITEL |

| ACTR | JULIA | FAWCETT |

| ACTR | THORA | TEMPLE |

+------+------------+-------------+

799 rows in set (0.00 sec)

该查询返回 799 个姓名，其中 599 行来自 customer 表，其他 200 行来自 actor 表。第一列（具有别名 typ）不是必需的，但我们添加该列以显示查询返回的每个姓名的源。

为了演示 union all 操作符不会删除重复项，下面的示例类似于上一个实例，但是对 actor 表执行两次相同的查询：

mysql> SELECT 'ACTR' typ, a.first_name, a.last_name

 -> FROM actor a 

-> UNION ALL 

-> SELECT 'ACTR' typ, a.first_name, a.last_name

 -> FROM actor a;

+------+-------------+--------------+

| typ | first_name | last_name |

+------+-------------+--------------+

| ACTR | PENELOPE | GUINESS |

| ACTR | NICK | WAHLBERG |

| ACTR | ED | CHASE |

| ACTR | JENNIFER | DAVIS |

| ACTR | JOHNNY | LOLLOBRIGIDA |

| ACTR | BETTE | NICHOLSON |

| ACTR | GRACE | MOSTEL |

...

| ACTR | BURT | TEMPLE |

| ACTR | MERYL | ALLEN |

| ACTR | JAYNE | SILVERSTONE |

| ACTR | BELA | WALKEN |

| ACTR | REESE | WEST |

| ACTR | MARY | KEITEL |

| ACTR | JULIA | FAWCETT |

| ACTR | THORA | TEMPLE |

+------+-------------+--------------+

400 rows in set (0.00 sec)

如你所见，结果中从 actor 表包含了两次 200 行数据，总共是 400 行。

在复合查询中不太可能包含两个重复查询，所以下面是另一个返回重复数据的复合查询的例子：

mysql> SELECT c.first_name, c.last_name

 -> FROM customer c

 -> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%' 

 -> UNION ALL  

-> SELECT a.first_name, a.last_name

 -> FROM actor a  

-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| JENNIFER | DAVIS |

| JENNIFER | DAVIS |

| JUDY | DEAN |

| JODIE | DEGENERES |

| JULIANNE | DENCH |

+------------+-----------+

5 rows in set (0.00 sec)

两个查询都返回缩写为 JD 的人名。结果集的五行中，有一行是重复的（JENNIFER DAVIS）。如果希望合并之后的表排除重复行，则需要使用 union 操作符而不是 union all：

mysql> SELECT c.first_name, c.last_name -

> FROM customer c

 -> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%' 

-> UNION 

-> SELECT a.first_name, a.last_name 

-> FROM actor a 

-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| JENNIFER | DAVIS |

| JUDY | DEAN |

| JODIE | DEGENERES |

| JULIANNE | DENCH |

+------------+-----------+

4 rows in set (0.00 sec)

此查询的结果集中只包含了四个不同的名字，而不是使用 union all 时返回的五行数据。

intersect 操作符

ANSI SQL 规范中包括用于执行交集的 intersect 操作符。不幸的是，MySQL 的 8.0 版本还没有实现 intersect 操作符。但若你使用的是 Oracle 或 SQL Server 2008 就可以使用这个操作符了。因为本书的所有示例用的都是 MySQL，所以本节中示例查询的结果集都是捏造的，不能在 8.0 及以下的任何版本中执行。我也避免显示 MySQL 提示符（mysql>），因为这些语句不是在 MySQL 服务器上执行的。

如果复合查询中的两个查询返回不重叠的数据集，则交集将是一个空集。考虑以下查询：

SELECT c.first_name, c.last_name

FROM customer c

WHERE c.first_name LIKE 'D%' AND c.last_name LIKE 'T%' 

INTERSECT

SELECT a.first_name, a.last_name

FROM actor a

WHERE a.first_name LIKE 'D%' AND a.last_name LIKE 'T%';

Empty set (0.04 sec)

虽然演员和客户都有首字母 DT，但这些集合是完全不重叠的，因此这两个集合的交集产生了空集。但是，如果我们使用缩写 JD，交集将包含一行数据：

SELECT c.first_name, c.last_name

FROM customer c

WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'

INTERSECT

SELECT a.first_name, a.last_name

FROM actor a

WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| JENNIFER | DAVIS |

+------------+-----------+

1 row in set (0.00 sec)

这两个查询的交集产生结果为“Jennifer Davis”，即两个待查询的结果集中唯一重复的值。

除了 intersect 操作符（删除重叠部分中所有重复行）之外，ANSI SQL 规范还允许调用 intersect all 操作符，该操作符不删除重复行。目前唯一一个实现 intersect all 操作符的数据库服务器是 IBM 的 DB2 Universal Server。

except 操作符

ANSI SQL 规范包括用于执行集合差操作的 except 操作符。同样不幸的是，MySQL 的 8.0 版本没有实现 except 操作符，因此本节与上一节一样采用捏造的示例进行演示。

注意：如果你使用的是 Oracle Database，则需要改用非 ANSI 兼容的 minus 操作符。

except 操作符返回第一个结果集减去与第二个结果集重合部分后的结果。以下是上一节中的示例，但使用 except 而不是 intersect，并且查询顺序颠倒：

SELECT a.first_name, a.last_name

FROM actor a

WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'

EXCEPT

SELECT c.first_name, c.last_name

FROM customer c

WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| JUDY | DEAN |

| JODIE | DEGENERES |

| JULIANNE | DENCH |

+------------+-----------+

3 rows in set (0.00 sec)

在该查询中，结果集由第一个查询的四行数据减去两个查询的结果集中都包含的“Jennifer Davis”组成。在 ANSI SQL 规范中还指定了一个 except all 操作符，但是同样只有 IBM 的 DB2 Universal Server 实现了该操作符。

except all 操作符有点难搞，所以下面的示例演示了如何处理重复数据。假设有两个如下所示的数据集：

Set A

+----------+

| actor_id |

+----------+

| 10 |

| 11 |

| 12 |

| 10 |

| 10 |

+----------+

Set B

+----------+

| actor_id |

+----------+

| 10 |

| 10 |

+----------+

操作 A except B 产生结果如下：

+----------+

| actor_id |

+----------+

| 11 |

| 12 |

+----------+

如果将操作改为 A except all B，结果将如下所示：

+----------+

| actor_id |

+----------+

| 10 |

| 11 |

| 12 |

+----------+

因此，这两种操作的区别在于，except 从集合 A 中删除所有重复数据，而 except all 根据重复数据在集合 B 中出现的次数进行删除。

集合操作规则

下面几节简述了使用复合查询需要注意的一些规则。

对复合查询结果进行排序

如果希望对复合查询的结果进行排序，可以在最后一个查询之后添加 order by 子句。在 order by 子句中指定列名时，需要从复合查询的第一个查询中选择列名。通常在复合查询中，两个查询的列名是相同的（不强制相同），如下所示：

mysql> SELECT a.first_name fname, a.last_name lname 

-> FROM actor a 

-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%' 

-> UNION ALL 

-> SELECT c.first_name, c.last_name -

> FROM customer c 

-> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%' 

-> ORDER BY lname, fname;

+----------+-----------+

| fname | lname |

+----------+-----------+

| JENNIFER | DAVIS |

| JENNIFER | DAVIS |

| JUDY | DEAN |

| JODIE | DEGENERES |

| JULIANNE | DENCH |

+----------+-----------+

5 rows in set (0.00 sec)

在本例中，两个查询中指定的列名不同。如果在 order by 子句的第二个查询中指定列名，将产生以下错误：

mysql> SELECT a.first_name fname, a.last_name lname 

-> FROM actor a 

-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%' 

-> UNION ALL 

-> SELECT c.first_name, c.last_name 

-> FROM customer c 

-> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%' 

-> ORDER BY last_name, first_name;

ERROR 1054 (42S22): Unknown column 'last_name' in 'order clause'

我建议为两个查询中的列提供相同的列别名，以避免此问题发生。

集合操作符的优先级

如果复合查询包含两个以上使用不同集合操作符的查询，则需要确定复合语句中查询的执行次序。考虑以下包含三个查询的复合语句：

mysql> SELECT a.first_name, a.last_name 

-> FROM actor a 

-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%' 

-> UNION ALL 

-> SELECT a.first_name, a.last_name 

-> FROM actor a 

-> WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'

 -> UNION 

-> SELECT c.first_name, c.last_name 

-> FROM customer c 

-> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| JENNIFER | DAVIS |

| JUDY | DEAN |

| JODIE | DEGENERES |

| JULIANNE | DENCH |

| MARY | TANDY |

| MENA | TEMPLE |

+------------+-----------+

6 rows in set (0.00 sec)

此复合查询包括三个返回非唯一名字集的查询，第一个和第二个查询用 union all 操作符分隔，而第二个和第三个查询用 union 操作符分隔。虽然 union 和 union all 操作符放置的位置似乎区别不大，但实际上是有区别的。下面是将两个集合操作符颠倒次序后的复合查询：

mysql> SELECT a.first_name, a.last_name

 -> FROM actor a 

-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'

 -> UNION -

> SELECT a.first_name, a.last_name 

-> FROM actor a -> WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'

 -> UNION ALL 

-> SELECT c.first_name, c.last_name 

-> FROM customer c 

-> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| JENNIFER | DAVIS |

| JUDY | DEAN |

| JODIE | DEGENERES |

| JULIANNE | DENCH |

| MARY | TANDY |

| MENA | TEMPLE |

| JENNIFER | DAVIS |

+------------+-----------+

7 rows in set (0.00 sec)

从结果来看，显然当使用不同的集合操作符时，复合查询的排列方式确实会产生不同的结果。通常，包含三个或三个以上查询的复合查询按自顶向下的顺序被解析和执行，但要注意以下几点：

• ANSI SQL 规范规定 intersect 操作符优先级高于其他集合操作符；

• 可以通过使用圆括号将多个查询封装使用，以指定查询的顺序。

MySQL 不允许在复合查询中使用括号，但如果你使用的是其他数据库服务器，则可以将相邻的查询封装在括号中，以改变默认复合查询自顶向下的处理，如下所示：

SELECT a.first_name, a.last_name

FROM actor a

WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%' 

UNION

(SELECT a.first_name, a.last_name  

FROM actor a  

WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%' 

 UNION ALL

 SELECT c.first_name, c.last_name

 FROM customer c

 WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%' )

对于这个复合查询，第二个和第三个查询将使用 union all 操作符进行组合，然后将它与第一个查询通过 union 操作符进行连接，以产生最终结果。