使用 SQL UNION 合并表
如何使用 SQL UNION 合并来自两个表的行。
什么是 SQL UNION
假设您想查看销售流程每个阶段的人数,但有一个问题:潜在客户和待开发客户在同一个表中(尽管有不同的筛选条件),试用数据在另一个表中,客户数据又在另一个表中。您需要统计所有这些数据并将信息合并到一个表中,但它们都有不同的逻辑并使用不同的来源。SQL 有一个特殊的运算符 UNION
,可以让您合并来自不同表的查询结果。
SQL 中有两种 UNION
运算符 - UNION
和 UNION ALL
。本文大部分内容适用于 UNION
和 UNION ALL
。
在深入学习高级 SQL 之前需要快速复习吗?请查看我们的SQL 速查表,了解核心命令和语法。它也非常适合与开始进行数据分析的同事分享。
UNION 与 JOIN 与 INTERSECT:何时使用
在 SQL 中有(至少)三种方法可以组合查询结果:JOIN
、UNION
和 INTERSECT
。它们有不同的用途。
- 当您想向结果添加更多行时,请使用
UNION
。 - 当您想向结果添加更多列时,请使用
JOIN
。 - 当您想查找表之间共同的行集时,请使用
INTERSECT
。
使用 SQL UNION
组合查询结果
要了解 UNION
的作用,让我们运行以下 SQL 查询(例如,您可以在 Metabase 中运行它)
SELECT "Kitten" as pet_type, 0.5 as pet_age
UNION ALL
SELECT "Puppy", 2
UNION ALL
SELECT "Bird", 17;
结果是
| pet_type | pet_age |
| -------- | ------- |
| Kitten | 0.5 |
| Puppy | 2 |
| Bird | 17 |
发生了什么
- SQL 执行
UNION
子句之间的每个SELECT
查询。 - 堆叠结果的行。
- 使用第一个查询中的别名(
AS "Kitten"
)作为列标题。
将被联合(这是一个词吗?..)的查询不需要返回单行 - 查询可以包含筛选器、分组、CTE 等。例如,假设您有两个查询
第一个查询
SELECT
breed,
count(*)
FROM kittens
GROUP BY breed;
第一个查询返回
| BREED | COUNT(*) |
| ------------------ | --------- |
| Domestic shorthair | 23 |
| Domestic longhair | 10 |
| Siamese | 3 |
第二个查询
SELECT
breed,
count(*)
FROM puppies
GROUP BY breed;
第二个查询返回
| BREED | COUNT(*) |
| ------------- | --------- |
| Beagle | 3 |
| Borzoi | 1 |
| Corgi | 5 |
| Very good boy | 37 |
那么这些查询的联合是
-- First query
SELECT
breed,
count(*)
FROM kittens
GROUP BY color
UNION ALL
-- Second query
SELECT
breed,
count(*)
FROM puppies
GROUP BY color
以及 UNION ALL
查询的结果
| BREED | COUNT(*) |
| ------------------ | --------- |
| Domestic shorthair | 23 |
| Domestic longhair | 10 |
| Siamese | 3 |
| Beagle | 3 |
| Borzoi | 1 |
| Corgi | 5 |
| Very good boy | 37 |
所以 UNION ALL
只是一个接一个地执行查询,并堆叠结果。在 UNION (ALL)
如何处理列名、列顺序和列类型方面有一些细微差别,但最基本的层面,它只是堆叠行。
UNION ALL
获取所有行,UNION
尝试去重
UNION ALL
获取查询结果并将它们堆叠在一起。UNION
(不带ALL
)做同样的事情,但也会尝试去重行。
例如,假设您有两个查询
第一个查询
SELECT
color,
count(*)
FROM kittens
GROUP BY color;
| COLOR | COUNT(*) |
| ------ | --------- |
| Orange | 18 |
| Black | 10 |
| White | 8 |
第二个查询
SELECT
color,
count(*)
FROM puppies
GROUP BY color;
| COLOR | COUNT(*) |
| ----- | --------- |
| Black | 10 |
| Brown | 23 |
| White | 15 |
那么 UNION ALL
的结果是
| COLOR | COUNT(*) |
| ------ | --------- |
| Orange | 18 |
| Black | 10 |
| White | 8 |
| Black | 10 |
| Brown | 23 |
| White | 15 |
一些颜色(“黑色”和“白色”)有多行。UNION
将尝试去重这些重复的行。
| COLOR | COUNT(*) |
| ------ | --------- |
| Orange | 18 |
| Black | 10 |
| White | 8 |
| Brown | 23 |
| White | 15 |
行 | Black | 10 |
出现在两个查询的结果中,所以 UNION
将删除其中一行。至于 White
行:即使颜色 White
出现两次,这些行也不是重复的。一行是 | White | 8 |
,另一行是 | White | 10 |
,所以 UNION
保留两者。
SQL UNION
在检索查询结果后执行去重,因此如果您在单个查询中有任何 LIMIT
或 ORDER
子句,UNION
的结果可能不会遵守这些子句。
为什么(通常)您应该优先使用 UNION ALL
而不是 UNION
当您使用 UNION
而不是 UNION ALL
时,SQL 将尝试去重结果 - 即使没有重复项。去重可能需要很长时间,因为 SQL 需要遍历每个查询中的所有记录。
只有当您确实需要删除重复行时才使用 UNION
。
完整表的 SQL UNION
您不能 UNION ALL
普通表。您只能联合查询结果:这行不通
--this won't work:
SELECT * FROM (
kittens
UNION ALL
puppies
);
如果您想将几个表堆叠在一起,您需要从每个表中选择所有记录。
-- UNION of two full tables
SELECT * FROM kittens
UNION ALL
SELECT * FROM puppies;
(尽管通常您应该避免在 UNION
中使用 SELECT *
,而是明确指定列,请参阅联合表必须具有相同数量的列)。
UNION ALL
示例:在 SQL 中构建销售漏斗
假设您将销售漏斗信息存储在三个表中
leads
表包含销售线索信息prospects
表包含潜在客户数据customers
表包含(您猜对了)客户信息
您可以对这些表中的每一个运行 COUNT(*)
,以独立查找潜在客户、待开发客户和客户的数量,但如果能将这些数字一起显示在一个表中(例如,如果您想了解转化率),那会更有帮助。这就是 UNION ALL
可以派上用场的地方
SELECT COUNT(*) from leads
UNION ALL
SELECT COUNT(*) from prospects
UNION ALL
SELECT COUNT(*) from customers;
此查询返回类似以下内容
| COUNT(*) |
| --------- |
| 1749 |
| 832 |
| 562 |
因此,您可以看到查询每个阶段的流失情况——例如,大约 48% 的潜在客户成为待开发客户。
我们在这里使用 UNION ALL
来加快查询速度,同时确保不会排除重复项:例如,如果您有一个转化率为 100% 的杀手级应用程序,并且您的潜在客户数量与客户数量相同,那么使用 UNION
而不是 UNION ALL
意味着那些重复的客户数量将被删除。
这些结果有两个问题:首先,列名 COUNT(*)
没有帮助;其次,这些数字没有太多上下文:哪个数字对应哪个阶段?所以让我们添加列名和一个额外的字段来存储阶段名称
SELECT 'Leads' as stage, COUNT(*) as ct from leads
UNION ALL
SELECT 'Prospects', COUNT(*) from prospects
UNION ALL
SELECT 'Customers', COUNT(*) from customers;
| stage | ct |
| --------- | ---- |
| Leads | 1749 |
| Prospects | 832 |
| Customers | 562 |
请注意,我们只需在 UNION
中的第一个查询中添加列名 - 这是因为 SQL UNION 仅从第一个查询中获取列名。
UNION ALL
示例:在 SQL 中跟踪用户流
为了一个更复杂的例子,假设您正在运营一个电子商务网站,并且您想跟踪人们通过以下流程的数量:
- 目录访问:查看产品目录。
- 产品访问:访问特定产品页面。
- 加入购物车:他们几乎给了您钱。
- 订单。他们确实给了您钱。
您的数据在多个表中
- 网站页面(如目录页面或产品页面)的视图在
page_views
表中。 - 按钮点击等事件在
events
表中单独跟踪。 - 实际客户订单在
orders
表中。
您可以计算产品列表页面的总页面浏览量,如下所示
-- Count all views to the catalog page
SELECT
COUNT(*)
FROM page_views
WHERE
page_url = 'https://www.whskr.co/catalog'
接下来,您想统计对特定产品页面的访问量 - 但不是所有访问量,而只是来自目录页面的访问量(与例如搜索或直接链接相对)。这使用相同的 page_views
表,但使用不同的过滤器
-- Count views to the product page coming from the catalog page
SELECT
COUNT(*)
FROM page_views
WHERE
page_url = 'https://www.whskr.co/product/smart-mouse-1234567'
AND referer_url = 'https://www.whskr.co/catalog'
接下来,您想统计将产品添加到购物车的人数,因此您筛选所有交互事件。它可能看起来像这样
-- Clicks on "Add to cart" button on the product page
SELECT
COUNT(*)
FROM events
WHERE
event_type = 'button_clicked'
AND event_subtype = 'add-to-cart'
AND source_page = 'https://www.whskr.co/product/smart-mouse-1234567'
最后,您想知道人们订购此产品的总次数
-- Number of orders for the product
SELECT COUNT(*)
FROM orders
WHERE product_id = 1234567
(这里我们假设下订单的唯一方式是从产品页面。)
现在,您想将所有这些计数放入一个表格中,以便比较通过每个阶段的人数。您还需要添加一个列来标识每个阶段。这就是 UNION (ALL)
的用武之地
-- Count all views to the catalog page
SELECT
'Catalog visits' as stage,
COUNT(*)
FROM page_views
WHERE
page_url = 'https://www.whskr.co/catalog'
UNION ALL
-- Count views to the product page coming from the catalog page
SELECT
'Product visits',
COUNT(*)
FROM page_views
WHERE
page_url = 'https://www.whskr.co/product/smart-mouse-1234567'
AND referer_url = 'https://www.whskr.co/catalog'
UNION ALL
-- Clicks on "Add to cart" button on the product page
SELECT
'Add to cart',
COUNT(*)
FROM interaction_events
WHERE
event_type = 'button_clicked'
AND event_subtype = 'cart'
AND source_page = 'https://www.whskr.co/product/smart-mouse-1234567'
UNION ALL
-- Number of orders for the product
SELECT
'Orders',
COUNT(*)
FROM orders
WHERE product_id = 1234567
这里我们将两个关于 page_views
表的查询与两个不同的筛选器、一个关于 events
表的查询以及一个关于 orders
表的查询结合起来。我们使用 UNION ALL
是因为这种情况不需要对数据进行去重。
结果将如下所示
| stage | COUNT(*) |
| -------------- | --------- |
| Catalog visits | 5842 |
| Product visits | 851 |
| Add to cart | 592 |
| Orders | 346 |
UNION ALL
示例:合并历史表中的数据
有时,当表格很大且查询时间很长时,人们会按日期将一个大表格拆分成几个表格。例如,您可能有一个单独的表格,其中包含每年的数据,例如 2023 年、2024 年、2025 年的数据表格…… 这样在 2025 年,您可能不会经常查询 1998 年的数据,因此除非绝对必要,否则无需让数据库处理这些数据。
但在这样的设置中,有时您确实需要检索所有年份的数据,这时 UNION (ALL)
就可以派上用场了。例如,如果您想获取所有年份的所有订单 ID、日期、总计,您可以编写如下查询(假设世界始于 2023 年)
SELECT id, created_at, total FROM orders_2023
UNION ALL
SELECT id, created_at, total FROM orders_2024
UNION ALL
SELECT id, created_at, total FROM orders_2023
UNION ALL
示例:添加汇总行
假设您正在使用以下查询按类别统计产品数量
SELECT
category,
COUNT(*) as ct
FROM products
GROUP BY category;
结果如下
| CATEGORY | ct |
| --------- | --- |
| Doohickey | 42 |
| Gadget | 53 |
| Gizmo | 51 |
| Widget | 54 |
您可以使用 UNION ALL
添加一行,其中包含记录的总计数
-- Query results
SELECT
category,
COUNT(*) as ct
FROM products
GROUP BY category
ORDER BY ct
UNION ALL
-- Total row
SELECT
'Total',
COUNT(*)
FROM products;
结果将如下所示
| CATEGORY | ct |
| --------- | --- |
| Doohickey | 42 |
| Gadget | 53 |
| Gizmo | 51 |
| Widget | 54 |
| Total | 200 |
您可以使用相同的思路添加其他类型的摘要。例如,您可以添加一行,显示跨类别的平均计数。
WITH ct_by_category AS (
SELECT
category,
COUNT(*) as ct
FROM products
GROUP BY category
)
SELECT category, ct FROM ct_by_category
UNION ALL
SELECT 'Average', AVG(ct) FROM ct_by_category;
在这里,我们将 COUNT
提取到 CTE 中,使查询更清晰。
SQL UNION
的工作原理
SQL UNION (ALL)
假设您知道自己在做什么,并且不会妨碍您 - 但也不会尝试变得智能并帮助您。SQL UNION (ALL)
只会执行您正在联合的查询(是的,现在这是一个词),并尝试堆叠结果行。如果出现问题 - 例如列数不同或列类型不匹配 - UNION (ALL)
将直接放弃。
以下是一些需要记住的事项
SQL UNION
从第一个查询中获取列
UNION (ALL)
将从第一个查询中获取列名,并忽略后续查询中的任何列名。
所以假设您有这样一个查询
SELECT 'Kitten' as pet_type, 0.5 as pet_age
UNION ALL
SELECT 'Bird' as category, 17 as age_in_years;
结果将是
| pet_type | pet_age |
| -------- | ------- |
| Kitten | 0.5 |
| Bird | 17 |
SQL UNION (ALL)
不会创建新列。因此,只要列数和数据类型匹配,SQL UNION (ALL)
就只会堆叠行。这可能不是您所期望的!
例如,看以下查询
SELECT 'Kitten' as pet_type, 0.5 as pet_age
UNION ALL
SELECT 'Senior Software Engineer' as job_title, 32768 as favorite_number;
您可能想要的结果
| pet_type | pet_age | job_title | favorite_number |
| -------- | ------- | ------------------------ | --------------- |
| Kitten | 0.5 | | |
| | | Senior Software Engineer | 32768 |
但查询实际返回的结果
| pet_type | pet_age |
| ------------------------ | ------- |
| Kitten | 0.5 |
| Senior Software Engineer | 32768 |
如果您想保留不同的列并将空值添加到没有相应数据的单元格中,您可以尝试使用全外连接。
SQL UNION
列必须按相同顺序排列
如果您有希望 SQL UNION (ALL)
相互“匹配”的列,则必须在每个查询中以相同的顺序放置它们。SQL UNION (ALL)
只会尝试将所有查询中第一列的所有值放在一起,第二列的所有值放在一起,依此类推,并且不会尝试根据名称、类型或任何其他特性来匹配列。
例如,这里查询创建者可能打算使用 pet_type
和 pet_age
两列,但两查询中的列顺序不同
-- This will cause an error in most databases:
SELECT 'Kitten' as pet_type, 0.5 as pet_age
UNION ALL
SELECT 17 as pet_age, 'Bird' as pet_type;
您可能期望的结果
-- Expected, but not actual the result of the query above
| pet_type | pet_age |
| -------- | ------- |
| Kitten | 0.5 |
| Bird | 17 |
但实际结果将取决于数据库。大多数数据库会报错,类似如下
ERROR: Data conversion error converting "Kitten";
这是您在 Metabase 示例数据库上收到的错误消息 - 其他数据库可能会给出不同的错误消息。
其他一些数据库(如 MySQL)会给出
| pet_type | pet_age |
| -------- | ------- |
| Kitten | 0.5 |
| 17 | Bird |
但结果绝不会是
-- Can't get this result with query above
| pet_type | pet_age |
| -------- | ------- |
| Kitten | 0.5 |
| Bird | 17 |
这里发生了什么?
-
首先,SQL
UNION
不关心第二个查询中的列名,所以它会忽略它们,并尝试将'Kitten'
和17
放在一列中,将0.5
和'Bird'
放在另一列中。 -
然后,由于类型不匹配——
'Kitten'
是字符串,17
是数字——大多数数据库会直接拒绝该操作。MySQL 则更宽容一些,会将17
转换为字符串'17'
。
当类型不不匹配时,这种行为尤其令人困惑,因为没有错误。例如,SQL 将毫不费力地执行以下查询,因为它所有列都是字符串
-- This query will not error out but will produce unexpected results
SELECT 'Kitten' as pet_type, 'Mittens' as pet_name
UNION ALL
SELECT 'Professor Beakman' as pet_name, 'Bird' as pet_type;
并返回
| pet_type | pet_name |
| ----------------- | -------- |
| Kitten | Mittens |
| Professor Beakman | Bird |
这可能不是您想要的。
联合表必须具有相同数量的列
如果查询结果的列数不同,则 UNION (ALL)
将无法工作 - 即使列之间存在重叠。例如,无论 ...
中包含什么,以下查询都将无法工作。
--- This query won't work
SELECT category, week_num, COUNT(*) FROM ...
UNION ALL
SELECT category, COUNT(*) FROM ...
第一个查询返回三列,第二个查询返回两列,因此 UNION ALL
无法堆叠这些结果。它不会为缺失的列创建一个额外的值为 null 的列。相反,您的数据库将返回一个错误,类似如下:
ERROR: each UNION query must have the same number of columns
当您使用 SELECT *
而不是明确指定列时,这可能会导致意外错误,特别是当您从 FROM
中选择的内容可能发生变化时。假设您有单独的表来存储每年订单信息:orders_2024
、orders_2025
等。要获取这些年来的所有订单,您可能会运行
SELECT * FROM orders_2023
UNION ALL
SELECT * FROM orders_2024
UNION ALL
SELECT * FROM orders_2025;
但如果有人向 orders_2025
添加新列,此查询将开始失败。
因此,当您使用 UNION (ALL)
时,请始终明确指定您要选择哪些列(这在编写任何 SELECT
时都是一个好习惯,无论是否使用 UNION
)
SELECT id, created_at, amount FROM orders_2023
UNION ALL
SELECT id, created_at, amount FROM orders_2024
UNION ALL
SELECT id, created_at, amount FROM orders_2025;
SQL UNION
中的列数据类型必须匹配
查询结果中列的类型必须匹配。因此,例如,UNION ALL
中每个查询的第一列将是数字,第二列将是文本等。如果类型不匹配——例如,其中一个查询的第一列是文本列,而另一个查询的第一列是数字列——则 UNION
查询可能无法工作。它是否工作取决于数据库和数据类型。如果类型不完全匹配,大多数数据库将显示错误。一些数据库(如 MySQL)将尝试类型转换(例如,数字到字符串),但如果无法转换,则会显示错误。隐式类型转换也可能导致意外结果。
例如,看以下查询
SELECT true
UNION ALL
SELECT DATE '2025-04-19'
这里我们尝试堆叠一个布尔值和一个日期。大多数数据库(例如 PostgreSQL)会直接报错。
MySQL 将改为返回
| TRUE |
| ---------- |
| 1 |
| 2025-04-19 |
这里 MySQL 将 TRUE
作为列的标题,因为我们没有提供任何其他选项。如果您检查返回列的数据类型,您会发现它现在是一个 VARCHAR
列。所以 MySQL 将 true
和 2025-04-19
都转换为字符串,并且 true
转换为字符串值 1
,因为 MySQL 实际上没有布尔数据类型,而是将布尔值存储为整数 1
或 0
,在此查询中 MySQL 也将其转换为字符串。所以您可以看到这可能会多么令人困惑!请确保 UNION
中列的数据类型匹配,以避免出现此类意外结果。