两个运算符都将两个查询的结果堆叠在一起，因此 SQL UNION 与 UNION ALL 可能看起来是一个次要的选择。它不是。多一个词就会改变你的结果以及数据库的工作强度。

我们在 SQLite 3.50 中运行了每个查询，显示的结果和计时来自这些运​​行。基本行为是跨主要数据库的标准 SQL。然而，时间会因您自己的数据库、数据和硬件而异，因此请以我们的为例。

简短的回答

UNION 组合两个结果集并仅返回不同的行。 UNION ALL 将它们组合起来并返回所有内容，包括重复项。这就是全部区别，本文的其余部分将讨论其重要性的原因。

UNION 与 UNION ALL 并排

样本数据

想象一下一家商店将在线订单和店内订单保存在不同的表中。请注意，Asha 有两份相同的在线订单和一份相同的实体店订单。

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

查看差异

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

五个输入行变为 UNION 的三行，所有五个输入行都保留为 UNION ALL。需要注意两点：

• UNION从整个结果中删除了重复项，包括第一个表中的重复项。它不仅仅是跨表比较。
• 仅当每个选定列 匹配时，行才算作重复行。

当您确实想要一个独特的列表时，UNION 是完全正确的。这为通过任一渠道购买的每位客户提供：

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

错误总数错误

这就是选择真正重要的原因。假设您想要总收入。上面的订单加起来是800。看看每个操作员给出的是什么：

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

UNION 版本错误了 200。因为我们只选择了 total 列，所以三个单独的 100 卢比订单看起来像是重复的，并合并为一个。没有错误消息，600 看起来是一个完全可信的数字。这种悄无声息的错误可能会在财务报告中停留数月之久。

双方都必须遵守的规则

两个运营商都有相同的要求：

1. 每个 SELECT 必须返回 相同数量的列。
2. 列按 位置 匹配，而不是按名称匹配，因此请保持它们的顺序相同。
3. 每个位置的数据类型必须兼容。
4. 结果列名称来自 first SELECT。
5. ORDER BY位于最后，它适用于整个结果。

违反第一条规则会产生错误。这是来自 SQLite 的消息：

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

第二条规则更危险，因为它可能会在没有错误的情况下失败。如果交换相同类型的两列，例如 first_name 和 last_name，查询将运行并且数据会出现错误。 SQLite 对类型也很宽松，而 PostgreSQL 等数据库则更严格，可能需要显式 CAST。

标记来源

一个非常实用的技巧是添加一个常量列来显示每行的来源。由于标签不同，来自不同表的行不能再被视为彼此的重复项。

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

一旦您的数据有了标签，您就可以使用 GROUP BY 将其分组以按渠道报告销售情况。如果您需要组合多个步骤，请将联合包装在 CTE 中以保持可读性。

性能：我们的测试中发生了什么

要删除重复项，数据库必须比较行，通常通过对行进行排序或散列来比较。 UNION ALL可以跳过此步骤。我们创建了两个表，每个表有 500,000 行，包含从 0 到 50,000 的随机数，因此存在大量重复项。我们将每个查询运行多次并保留最佳时间。

首先，我们计算结果，以便将行发送回我们的程序所花费的时间不会影响比较：

巨大差距的部分原因是 SQLite 可以在不读取每一行的情况下计算出该计数，因此请不要将其理解为“快数千倍”。不过，它确实展示了核心思想：UNION 必须做实际工作，而 UNION ALL 没有。

然后我们将所有行提取到Python中，结果发生了翻转：

在这里，UNION ALL 花费了更长的时间，因为它交付的行数是原来的 20 倍。重复数据删除并不是减慢速度的原因。处理一百万行是。因此，诚实的规则是，UNION ALL 对于数据库的计算来说更便宜，但结果的大小也很重要。许多在线图表将其简化为“UNION ALL 总是更快”，而我们的测试表明了为什么它没那么简单。

也不要仅根据速度进行选择。首先选择给出正确答案的操作员，然后进行调整。如果查询速度很慢，请阅读我们的 SQL 查询优化指南。

何时使用每个

在以下情况下使用 UNION ALL：

• 您正在合并来自类似表的数据，例如每月分区。
• 之后您将计算总数或计数。
• 您知道输入不能重叠。
• 重复是有意义的，就像日志或审计记录一样。

在以下情况下使用 UNION：

• 您需要一个唯一的列表，例如两个系统中的每个电子邮件地址。
• 重叠的来源多次包含相同的事实。

将UNION ALL视为默认值，并有意使用UNION。向每个分支添加一个 WHERE 子句以尽早进行过滤，这对两者都有帮助。

相交和除外

两个相关的集合运算符回答不同的问题。两者都遵循相同的列规则：

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle 传统上使用 MINUS 而不是 EXCEPT，并且对这些运算符的支持因数据库和版本而异。

联合与连接

这些经常被混淆，但它们结合了不同方向的数据：

常见错误

• 习惯使用UNION。这可能会降低你的速度，或者删除有效的行。
• 选择的列太少。 省略 ID 会使不同的行看起来相同。
• 列顺序不匹配。 值可能会出现在错误的列中，但不会出现任何错误。
• 为每个分支添加 ORDER BY。 在最后排序一次。
• 使用 UNION 修补错误的连接。 如果由于连接而出现重复，请修复连接。
• 假设结果顺序。 如果没有 ORDER BY，则不能保证任一运算符的行顺序。

最后的话

每次问一个问题：相同的行是相同的事实，还是不同的事实？如果它们是分开的，或者不可能重叠，请使用 UNION ALL。如果您需要唯一的列表，请使用 UNION。要检查您自己的报告，请在代码中搜索 SUM 或 COUNT 附近的 UNION，并确认这些总计确实正确。

常见问题

继续学习