SQL UNION 操作详解：合并查询结果的实用指南

2 年 ago

宇, 华

5 minutes

引言

许多数据库会根据其含义和上下文将信息分布在不同的表中。通常，在检索数据库中存储的数据时，您需要同时参考多个表格。

结构化查询语言（SQL）提供了多种方法从不同的表中检索数据，例如集合操作。更具体地说，集合运算符UNION在大多数关系型数据库系统中得到广泛支持。UNION操作接受具有匹配列的两个查询的结果，并将它们合并为一个结果。

在本指南中，您将使用UNION操作同时从多个表中检索数据，然后将结果进行合并。您还将结合UNION运算符和过滤来对结果进行排序。

先决条件

为了遵循本指南，您需要一台运行基于SQL的关系数据库管理系统（RDBMS）的电脑。本指南中的说明和示例已在以下环境中验证：

一台运行 Ubuntu 20.04 的服务器，具有非 root 用户、管理权限和使用 UFW 配置的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
在服务器上安装并安全配置了 MySQL，如如何在 Ubuntu 20.04 上安装 MySQL 中所述。本指南已使用非 root MySQL 用户进行验证，该用户是按照第 3 步中描述的过程创建的。
对执行 SELECT 查询以从数据库中选择数据有基本了解，如我们的如何在 SQL 中从表中选择行指南中描述。

注意：请注意，许多关系数据库管理系统（RDBMS）使用自己独特的 SQL 实现。虽然本教程中提到的命令在大多数 RDBMS 上都可以使用，并且是标准 SQL 语法的一部分，但是如果您在除 MySQL 之外的系统上测试它们，确切的语法或输出可能会有所不同。您还需要一个带有一些加载了示例数据的数据库，以便您可以练习使用 UNION 操作。我们鼓励您阅读以下关于连接到 MySQL 服务器和设置示例数据库的部分，以了解有关连接到 MySQL 服务器并创建用于本指南中示例的示例数据库的详细信息。

连接到 MySQL 并设置一个示例数据库

在本部分，您将连接到一个 MySQL 服务器并创建一个示例数据库，以便您可以按照本指南中的示例操作。

如果您的 SQL 数据库系统在远程服务器上运行，则需要从本地机器通过 SSH 登录到服务器：

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示符，将 sammy 替换为您的 MySQL 用户帐户的名称。

mysql -u sammy -p

创建一个名为 bookstore 的数据库。

CREATE DATABASE bookstore;

如果成功创建数据库，您将会收到以下类似的输出：

输出

Query OK, 1 row affected (0.01 sec)

要选择 bookstore 数据库，请运行以下 USE 语句：

USE bookstore;

您将会收到以下的输出结果：

输出

Database changed

在选择数据库之后，您可以在其中创建示例表。本指南的目的是使用一个想象中的书店，它既提供图书购买服务，也提供租赁服务。这两项服务是分开管理的，因此购买和租赁的数据存储在不同的表中。

注意：本示例中的数据库模式仅为教育目的而简化。在实际情况中，表结构可能更复杂，并涉及主键和外键。有关数据库如何组织数据的更多信息，请参阅我们的关系数据库理解教程。

第一个表 book_purchases 将包含有关购买的书籍和购买者的数据。它将拥有四列：

purchase_id: 此列保存购买标识符，由 int 数据类型表示。此列将成为表的主键，每个值都将成为其相应行的唯一标识符。
customer_name: 此列将保存客户姓名，使用 varchar 数据类型表示，最大长度为 30 个字符。
book_title: 此列将保存所购图书的标题，使用 varchar 数据类型表示，最大长度为 200 个字符。
date: 使用 date 数据类型，此列将保存每次购买的日期。

使用以下命令创建样本表。

CREATE TABLE book_purchases (
 purchase_id int,
 customer_name varchar(30),
 book_title varchar(40),
 date date,
 PRIMARY KEY (purchase_id)
);

如果以下输出打印出来，说明第一个表已经被创建：

输出

这是文章《如何在SQL中使用联合》的第2部分（共7部分）。

Query OK, 0 rows affected (0.00 sec)

第二个表将命名为book_leases（书籍租借），用于存储有关借阅书籍的信息。它的结构与前一个表类似，但租借信息通过两个不同的日期进行描述：租借开始日期和租借结束日期。为了表示这一点，book_leases表将包含五列：

lease_id: 此列存储租借标识符，数据类型为int。此列将成为表的主键，每个值都是其对应行的唯一标识符。
customer_name: 此列存储客户姓名，数据类型为varchar，最大长度为30个字符。
book_title: 此列存储借阅书籍的标题，数据类型为varchar，最大长度为200个字符。
date_from: 此列使用date数据类型，存储租借的开始日期。
date_to: 此列使用date数据类型，存储租借的结束日期。

使用以下命令创建第二个表：

CREATE TABLE book_leases (
lease_id INT,
customer_name VARCHAR(30),
book_title VARCHAR(40),
date_from DATE,
date_to DATE,
PRIMARY KEY (lease_id)
);

以下输出确认了第二张表的创建。

输出

这是文章《如何在SQL中使用联合》的第3部分（共7部分）。

Query OK, 0 rows affected (0.00 sec)

之后，运行以下INSERT INTO操作，为购买表加载一些示例数据。



 	INSERT INTO book_purchases
 	VALUES
 	(1, 'sammy', 'The Picture of Dorian Gray', '2022-10-01'),
 	(2, 'sammy', 'Pride and Prejudice', '2022-10-04'),
 	(3, 'sammy', 'The Time Machine', '2022-09-23'),
 	(4, 'bill', 'Frankenstein', '2022-07-23'),
 	(5, 'bill', 'The Adventures of Huckleberry Finn', '2022-10-01'),
 	(6, 'walt', 'The Picture of Dorian Gray', '2022-04-15'),
 	(7, 'walt', 'Frankenstein', '2022-10-13'),
 	(8, 'walt', 'Pride and Prejudice', '2022-10-19');

插入操作将把具有指定值的8条购买记录添加到book_purchases表中。下面的输出表明已成功添加了全部八行。

输出

这是文章《如何在SQL中使用联合》的第4部分（共7部分）。

Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

然后将一些样本数据插入到book_leases表中。

INSERT INTO book_leases
VALUES
(1, ‘sammy’, ‘Frankenstein’, ‘2022-09-14’, ‘2022-11-14’),
(2, ‘sammy’, ‘Pride and Prejudice’, ‘2022-10-01’, ‘2022-12-31’),
(3, ‘sammy’, ‘The Adventures of Huckleberry Finn’, ‘2022-10-01’, ‘2022-12-01’),
(4, ‘bill’, ‘The Picture of Dorian Gray’, ‘2022-09-03’, ‘2022-09-18’),
(5, ‘bill’, ‘Crime and Punishment’, ‘2022-09-27’, ‘2022-12-05’),
(6, ‘kim’, ‘The Picture of Dorian Gray’, ‘2022-10-01’, ‘2022-11-15’),
(7, ‘kim’, ‘Pride and Prejudice’, ‘2022-09-08’, ‘2022-11-17’),
(8, ‘kim’, ‘The Time Machine’, ‘2022-09-04’, ‘2022-10-23’);

你将收到以下输出，确认样本数据已被添加。

输出

Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

租约和购买与相似的顾客和图书标题有关，这将有助于演示UNION运算符的行为。

有了这些，你就可以继续阅读本指南，并开始在SQL中使用UNION操作。

理解SQL中`UNION`运算符的语法

在SQL中，UNION运算符指示数据库将通过单独的SELECT查询检索到的两个独立结果集合并为一个结果集，其中包含来自两个查询返回的行。

注意：数据库不限制与UNION一起使用的SELECT查询的复杂性。数据检索查询可以包括JOIN语句、聚合函数或子查询。通常，UNION用于合并来自复杂语句的结果。为了教育目的，本指南中的示例将使用简单的SELECT查询，以便重点关注UNION运算符的行为。

这是文章《如何在SQL中使用联合》的第5部分（共7部分）。

下面的例子展示了一个包含UNION运算符的SQL语句的通用语法。


 	SELECT column1, column2 FROM table1
 	UNION
 	SELECT column1, column2 FROM table2;

这个SQL片段首先从table1返回两列，接着是UNION运算符和第二个SELECT语句。第二个SELECT查询也返回两列，但它们来自table2。UNION关键字告诉数据库分别执行前面和后面的查询，然后将它们的结果集合并。整个代码片段，包括两个SELECT查询和它们之间的UNION关键字，是一个单独的SQL语句。因此，第一个SELECT查询不以分号结束，分号只出现在整个语句的末尾。

举个例子，假设您想列出所有购买或租赁过一本书的客户。购买记录存储在book_purchases表中，而租赁记录存储在book_leases表中。您可以执行以下查询：


 	SELECT customer_name FROM book_purchases
 	UNION
 	SELECT customer_name FROM book_leases;

这是这个查询的结果集。

输出

+---------------+
| customer_name |
+---------------+
| sammy         |
| bill          |
| walt          |
| kim           |
+---------------+
4 rows in set (0.000 sec)

这个结果表明，Sammy、Bill、Walt和Kim都在某个时间购买或租借过书籍。要理解这个结果集是如何产生的，您可以尝试分别执行两个SELECT语句：一个用于购买记录，一个用于租赁记录。

运行以下查询以返回购买书籍的客户。


 	SELECT customer_name FROM book_purchases;

下面的输出将打印到屏幕上：

输出

+---------------+
| customer_name |
+---------------+
| sammy         |
| sammy         |
| sammy         |
| bill          |
| bill          |
| walt          |
| walt          |
| walt          |
+---------------+
8 rows in set (0.000 sec)

Sammy、Bill 和 Walt 购买了书，但是 Kim 没有购买。

接下来，运行查询以返回租借图书的客户。


 	SELECT customer_name FROM book_leases;

下面的输出将打印到屏幕上：

输出

+---------------+
| customer_name |
+---------------+
| sammy         |
| sammy         |
| sammy         |
| bill          |
| bill          |
| kim           |
| kim           |
| kim           |
+---------------+
8 rows in set (0.000 sec)

租赁表中涉及的人包括 Sammy、Bill 和 Kim，但 Walt 从未租借过书。将这两个结果合并，您就可以获得租赁和购买的综合数据。

使用UNION与单独执行两个查询之间的重要区别在于，UNION除了合并结果外，还会删除重复的值：最终结果中没有重复的客户姓名。

为了正确地使用UNION合并两个独立查询的结果，这两个查询都应以相同的格式返回结果。一些不一致会导致数据库引擎报错，而另一些则会给出不符合查询意图的结果。

考虑以下两个例子：

联合查询时列不匹配

尝试执行一个UNION操作，其中包含一个返回单列的SELECT语句和另一个返回两列的SELECT语句。


 	SELECT purchase_id, customer_name FROM book_purchases
 	UNION
 	SELECT customer_name FROM book_leases;

数据库服务器将返回一个错误。

输出

The used SELECT statements have a different number of columns

不可能对具有不同列数的结果集执行联合操作。

列顺序不匹配的UNION

尝试执行一个UNION操作，该操作会将返回相同值但顺序不同的两个SELECT语句进行合并。


 	SELECT customer_name, book_title FROM book_purchases
 	UNION
 	SELECT book_title, customer_name FROM book_leases;

数据库服务器不会返回错误，但结果集将不正确。

输出

这是文章《如何在SQL中使用联合》的第6部分（共7部分）。

+------------------------------------+------------------------------------+
| customer_name                      | book_title                         |
+------------------------------------+------------------------------------+
| sammy                              | The Picture of Dorian Gray         |
| sammy                              | Pride and Prejudice                |
| sammy                              | The Time Machine                   |
| bill                               | Frankenstein                       |
| bill                               | The Adventures of Huckleberry Finn |
| walt                               | The Picture of Dorian Gray         |
| walt                               | Frankenstein                       |
| walt                               | Pride and Prejudice                |
| Frankenstein                       | sammy                              |
| Pride and Prejudice                | sammy                              |
| The Adventures of Huckleberry Finn | sammy                              |
| The Picture of Dorian Gray         | bill                               |
| Crime and Punishment               | bill                               |
| The Picture of Dorian Gray         | kim                                |
| Pride and Prejudice                | kim                                |
| The Time Machine                   | kim                                |
+------------------------------------+------------------------------------+
16 rows in set (0.000 sec)

在这个例子中，UNION 操作将第一个查询的第一列与第二个查询的第一列合并，并将第二列做相同的操作，从而将顾客名称和书籍标题混合在一起。

在SQL中使用WHERE子句和ORDER BY子句与UNION结合

在前面的例子中，你合并了代表两个对应表中所有行的结果集。通常，在合并结果之前，你需要筛选行。使用UNION运算符合并的SELECT语句可以使用WHERE子句来实现筛选。

假设你想通过你的书店购买或租赁的方式知道Sammy读了哪些书。运行以下查询：


 	SELECT book_title FROM book_purchases
 	WHERE customer_name = 'Sammy'
 	UNION
 	SELECT book_title FROM book_leases
 	WHERE customer_name = 'Sammy';

两个SELECT查询均包括WHERE子句，从两个不同的表中过滤出只有Sammy的购买和租赁记录。该查询的结果集将打印如下：

输出

+------------------------------------+
| book_title                         |
+------------------------------------+
| The Picture of Dorian Gray         |
| Pride and Prejudice                |
| The Time Machine                   |
| Frankenstein                       |
| The Adventures of Huckleberry Finn |
+------------------------------------+
5 rows in set (0.000 sec)

再次强调，UNION 确保结果列表中不会出现重复项。您可以使用 WHERE 子句来限制在两个 SELECT 查询中返回哪些行，或者只在其中一个查询中返回。此外，WHERE 子句可以引用两个语句中的不同列和条件。

通过UNION操作返回的结果没有特定的顺序。要改变这一点，可以利用ORDER BY子句。排序是在最终合并的结果上执行的，而不是在各个查询上执行。

在检索到Sammy购买或租赁的所有书籍清单后，按书名字母顺序对书籍标题进行排序，执行以下查询。


 	SELECT book_title FROM book_purchases
 	WHERE customer_name = 'Sammy'
 	UNION
 	SELECT book_title FROM book_leases
 	WHERE customer_name = 'Sammy'
 	ORDER BY book_title;

以下输出将打印到屏幕上：

输出

+------------------------------------+
| book_title                         |
+------------------------------------+
| Frankenstein                       |
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
| The Time Machine                   |
+------------------------------------+
5 rows in set (0.001 sec)

这次，结果是按照包含来自两个SELECT查询的book_title列的合并结果的顺序返回的。

使用 UNION ALL 保留重复项

如前面的示例所示，UNION操作符会自动从结果中删除重复的行。然而，有时候这种行为并不符合您的预期或查询的意图。例如，假设您对于在2022年10月1日购买或租赁的书籍感兴趣。要检索这些标题，您可以参考之前的类似示例：


 	SELECT book_title FROM book_purchases
 	WHERE date = '2022-10-01'
 	UNION
 	SELECT book_title FROM book_leases
 	WHERE date_from = '2022-10-01'
 	ORDER BY book_title;

你将得到以下的结果：

输出

这是文章《如何在SQL中使用联合》的第7部分（共7部分）。

+------------------------------------+
| book_title                         |
+------------------------------------+
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
+------------------------------------+
3 rows in set (0.001 sec)

返回的图书标题是正确的，但结果无法告诉您这些图书是仅购买、仅租赁还是两者都有。如果有些图书既购买又租赁，它们的标题将同时出现在图书购买和图书租赁表中。然而，由于UNION操作会去除重复行，这些信息在结果中被丢失。

幸运的是，SQL有一种方法可以改变这种行为并保留重复的行。您可以使用UNION ALL运算符将两个查询的结果合并在一起，而不会删除重复的行。UNION ALL的工作方式类似于UNION，但在存在多个相同值的情况下，所有值都将出现在结果中。

运行相同的查询，但将UNION更改为UNION ALL。

SELECT book_title FROM book_purchases
WHERE date = ‘2022-10-01’
UNION ALL
SELECT book_title FROM book_leases
WHERE date_from = ‘2022-10-01’
ORDER BY book_title;

这一次，最终的清单将会更长。

输出

+------------------------------------+
| book_title                         |
+------------------------------------+
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
| The Picture of Dorian Gray         |
+------------------------------------+
5 rows in set (0.000 sec)

两本书《哈克贝利·费恩历险记》和《道林·格雷的画像》在结果集中出现了两次。这意味着这些标题在书籍购买和书籍租赁表中均出现了。对于重复的条目，可以假设它们在那天被租借和购买。

根据您是否希望去除重复项或保留重复项，您可以在UNION和UNION ALL操作符之间进行选择，这两者可以互换使用。

注意：执行UNION ALL比执行UNION更快，因为数据库不需要扫描结果集以去重。如果你要合并两个SELECT查询的结果，而你知道这些结果不会包含重复行，使用UNION ALL可以在较大的数据集上带来明显的性能提升。

结论

通过遵循本指南，您使用UNION和UNION ALL操作从多个表中检索了数据。您还使用了WHERE子句来筛选结果，并使用ORDER BY子句对其进行排序。最后，您了解了如果SELECT语句产生不同的数据格式可能出现的错误和意外行为。

尽管这里包含的命令应该适用于大多数关系型数据库，但请注意每个SQL数据库都使用其自己独特的语言实现。（有关这些差异的更多信息，请参阅我们的指南《SQLite vs MySQL vs PostgreSQL：关系数据库管理系统的比较》。）您应该查阅您所使用的关系型数据库管理系统的官方文档，以获取每个命令及其完整选项集的更全面描述。

如果您想了解更多关于SQL语言及其使用的不同概念方面的知识，我们鼓励您查阅《如何使用SQL系列》中的其他指南。

#SQL #UNION #数据合并 #数据库 #查询

引言