使用 SQL UNION 合并表

如何使用 SQL UNION 合并来自两个表的行。

什么是 SQL UNION

假设您想查看销售流程每个阶段的人数,但有一个问题:潜在客户和待开发客户在同一个表中(尽管有不同的筛选条件),试用数据在另一个表中,客户数据又在另一个表中。您需要统计所有这些数据并将信息合并到一个表中,但它们都有不同的逻辑并使用不同的来源。SQL 有一个特殊的运算符 UNION,可以让您合并来自不同表的查询结果。

SQL 中有两种 UNION 运算符 - UNIONUNION ALL。本文大部分内容适用于 UNIONUNION ALL

在深入学习高级 SQL 之前需要快速复习吗?请查看我们的SQL 速查表,了解核心命令和语法。它也非常适合与开始进行数据分析的同事分享。

UNION 与 JOIN 与 INTERSECT:何时使用

在 SQL 中有(至少)三种方法可以组合查询结果:JOINUNIONINTERSECT。它们有不同的用途。

  • 当您想向结果添加更多时,请使用 UNION
  • 当您想向结果添加更多时,请使用 JOIN
  • 当您想查找表之间共同的行集时,请使用 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      |

发生了什么

  1. SQL 执行 UNION 子句之间的每个 SELECT 查询。
  2. 堆叠结果的行。
  3. 使用第一个查询中的别名(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 在检索查询结果后执行去重,因此如果您在单个查询中有任何 LIMITORDER 子句,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 中跟踪用户流

为了一个更复杂的例子,假设您正在运营一个电子商务网站,并且您想跟踪人们通过以下流程的数量:

  1. 目录访问:查看产品目录。
  2. 产品访问:访问特定产品页面。
  3. 加入购物车:他们几乎给了您钱。
  4. 订单。他们确实给了您钱。

您的数据在多个表中

  • 网站页面(如目录页面或产品页面)的视图在 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_typepet_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_2024orders_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 将 true2025-04-19 都转换为字符串,并且 true 转换为字符串值 1,因为 MySQL 实际上没有布尔数据类型,而是将布尔值存储为整数 10,在此查询中 MySQL 也将其转换为字符串。所以您可以看到这可能会多么令人困惑!请确保 UNION 中列的数据类型匹配,以避免出现此类意外结果。

延伸阅读

这有帮助吗?

感谢您的反馈!
分析师每周技巧
获取可行的见解
关于 AI 和数据的资讯,直接发送到您的收件箱
© . This site is unofficial and not affiliated with Metabase, Inc.