如何使用SQL系列 之 如何在SQL中使用联合(UNION)_union sql

引言

许多数据库传播信息在不同的表基于它们的含义和上下文。在检索数据库中保存的数据信息时，经常需要同时引用多个表。

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

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

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

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

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器：

ssh sammy@your_server_ip
1

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称：

mysql -u sammy -p
1

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

CREATE DATABASE bookstore;
1

如果数据库成功创建,您将收到这样的输出：

OutputQuery OK, 1 row affected (0.01 sec)
1

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

USE bookstore;
1

OutputDatabase changed
1

选择数据库后，您可以在其中创建示例表。在本指南中，你将使用一个虚构的书店，它既提供图书购买，也提供图书租赁。两个服务都是分开管理的;因此，有关购买和租赁的数据存储在不同的表中。

注意：为了便于学习，本例中的数据库模式被简化了。在实际场景中，表结构将更加复杂，并涉及主键和外键。有关数据库如何组织数据的更多信息，请参阅我们关于理解关系数据库的教程。

第一个表book_purchases将包含购买的书籍和购买的顾客的数据。它将包含4列：

• 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)
);
1
2
3
4
5
6
7

如果输出如下，说明已经创建了第一张表：

OutputQuery OK, 0 rows affected (0.00 sec)
1

第二个表名为book_leases，它将存储有关借阅书籍的信息。它的结构与前一个类似，但租约的特征是两个不同的日期:租约日期和租约持续时间。为了表示这一点，表将包含5列：

• 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)
);
1
2
3
4
5
6
7
8

下面的输出确认了第二个表的创建：

OutputQuery OK, 0 rows affected (0.00 sec)
1

接下来，通过运行以下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');
1
2
3
4
5
6
7
8
9
10

INSERT INTO操作将向book_purchases表中添加8个指定值的购买记录。下面的输出表明已经添加了8行数据：

OutputQuery OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0
1
2

然后在book_lease 表中插入一些示例数据：

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');
1
2
3
4
5
6
7
8
9
10

你将收到以下输出，它确认示例数据已被添加：

OutputQuery OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0
1
2

租赁和购买涉及相似的客户和书籍，这将有助于演示UNION操作符的行为。

理解UNION操作符的语法

SQL中的UNION操作符告诉数据库将通过单独的SELECT查询检索到的两个单独的结果集合并为一个结果集，其中包含两个查询返回的行。

注意：数据库不会限制UNION中SELECT查询的复杂性。数据检索查询可以包括JOIN语句、聚合或子查询。通常，UNION用于合并复杂语句的结果。出于教学目的，本指南中的示例将使用SELECT查询来关注UNION操作符的行为。

下面的例子展示了包含UNION操作符的SQL语句的通用语法：

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
1
2
3

这个SQL片段以一条从table1返回两列的SELECT语句开始，接着是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;
1
2
3

下面是这个查询的结果集：

Output+---------------+
| customer_name |
+---------------+
| sammy         |
| bill          |
| walt          |
| kim           |
+---------------+
4 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9

这个输出表明Sammy、Bill、Walt和Kim在某个时间点购买或租了书。要理解这个结果集是如何生成的，请尝试分别执行两个SELECT语句:一次针对purchase，一次针对lease。

运行下面的查询来返回购买图书的顾客：

SELECT customer_name FROM book_purchases;
1

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

Output+---------------+
| customer_name |
+---------------+
| sammy         |
| sammy         |
| sammy         |
| bill          |
| bill          |
| walt          |
| walt          |
| walt          |
+---------------+
8 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

Sammy, Bill和 Walt 购买书籍,但是Kim 没有。

接下来，运行查询来返回租书的顾客：

SELECT customer_name FROM book_leases;
1

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

Output+---------------+
| customer_name |
+---------------+
| sammy         |
| sammy         |
| sammy         |
| bill          |
| bill          |
| kim           |
| kim           |
| kim           |
+---------------+
8 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

租赁表指Sammy, Bill和Kim,但Walt从未借的书。通过组合两个答案，您可以得到租赁和购买的数据。

使用UNION和单独执行两个查询之间的重要区别是，UNION除了合并结果之外，还删除了重复的值:在结果中没有重复的客户名称。

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

考虑下面两个例子：

列计数不匹配的UNION

试着在返回单列的SELECT语句和返回两列的UNION语句之间执行：

SELECT purchase_id, customer_name FROM book_purchases
UNION
SELECT customer_name FROM book_leases;
1
2
3

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

OutputThe used SELECT statements have a different number of columns
1

无法对具有不同列数的结果集执行UNION操作。

列顺序不匹配的UNION

试着在两个返回相同值但顺序不同的SELECT语句之间执行UNION：

SELECT customer_name, book_title FROM book_purchases
UNION
SELECT book_title, customer_name FROM book_leases;
1
2
3

数据库服务器不会返回错误，但结果集不会是正确的：

Output+------------------------------------+------------------------------------+
| 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在这个例子中，UNION操作将第一个查询的第一列与第二个查询的第一列合并，并对第二个列执行相同的操作，将客户姓名和图书名称混合在一起。

使用WHERE子句并与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';
1
2
3
4
5

两个SELECT查询都包含WHERE子句，它从两个不同的表中过滤数据，只包含Sammy的purchase和lease。此查询的结果集将打印如下：

Output+------------------------------------+
| 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)
1
2
3
4
5
6
7
8
9
10

再一次，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;
1
2
3
4
5
6

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

Output+------------------------------------+
| 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)
1
2
3
4
5
6
7
8
9
10

这一次，返回的结果是基于包含两个SELECT查询合并结果的book_title列的顺序。

使用UNION ALL来保留副本

如前面的例子所示，UNION操作符会自动从结果中删除重复的行。然而，有时这种行为并不是您期望或打算通过查询实现的。例如,假设您感兴趣的书籍,购买或租赁10月1日,2022年。要取得这些标题，可以像之前一样使用类似的例子：

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;
1
2
3
4
5
6

You will get the following results：

Output+------------------------------------+
| book_title                         |
+------------------------------------+
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
+------------------------------------+
3 rows in set (0.001 sec)
1
2
3
4
5
6
7
8

归还的书名是正确的，但结果不会告诉你这些书是只买的、只租的，还是两者都有。如果有些书同时购买和租赁，它们的书名将同时出现在book_purchase和book_lease表中。然而，由于UNION删除了重复的行，这些信息在结果中丢失了。

幸运的是，SQL有办法改变这种行为，保留重复的行。你可以使用UNION ALL操作符来合并两个查询的结果，而不会删除重复的行。UNION ALL的工作原理与UNION类似，但当相同的值出现多次时，ALL将出现在结果中。

运行相同的查询，但将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;
1
2
3
4
5
6

这一次，得到的列表变长了：

Output+------------------------------------+
| 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)
1
2
3
4
5
6
7
8
9
10

《The Adventures of Huckleberry Finn》和《The Picture of Dorian Gray》这两本书在结果集中出现了两次。这意味着这些标题同时出现在book_purchase 和book_lease 表中。对于重复的条目，您可以假设它们在当天已经被租赁和购买。

根据你是想删除还是保留重复项，你可以在UNION和UNION ALL操作符之间进行选择，它们可以互换使用。

注意：执行工会所有的速度比执行“联盟”,作为数据库不需要对重复扫描结果集。如果你正在合并两个SELECT查询的结果，并且你知道它们不会包含任何重复的行，那么在更大的数据集上使用UNION ALL可以带来明显的性能提升。

总结

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

虽然这里包含的命令应该适用于大多数关系数据库，但请注意，每个SQL数据库都使用自己独特的语言实现。