English 中文 العربية 日本語 Português

PostgreSQL: PostgreSQL集合操作与组合查询

最后更新：2026-08-26

1. 你将学到

2. 故事

Bob 是电商平台的数据分析师。CEO 问了他三个问题：

  1. Q1、Q2、Q3 三个季度的热销产品有哪些？（UNION ALL 合并）
  2. 哪些产品每个季度都是热销品？（INTERSECT 交集 = 常青款）
  3. Q1 热销但 Q2 不再热销的产品有哪些？（EXCEPT 差集 = 流失款）

Bob 发现这些问题恰好对应 SQL 的三大集合操作：UNION、INTERSECT、EXCEPT。

3. Concept

(1) 集合操作概览

操作 含义 去重 类比
UNION 合并结果集 是 A ∪ B
UNION ALL 合并结果集 否 A ∪ B（含重复）
INTERSECT 交集 是 A ∩ B
INTERSECT ALL 交集 否 A ∩ B（含重复计数）
EXCEPT A 中有但 B 中没有 是 A - B
EXCEPT ALL A 中有但 B 中没有 否 A - B（含重复计数）
100% 
flowchart TD
    subgraph Union
        U1((A)) --- U2((B))
        U1 & U2 --> U3["A ∪ B"]
    end
    subgraph Intersect
        I1((A)) --- I2((B))
        I1 ∩ I2 --> I3["A ∩ B"]
    end
    subgraph Except
        E1((A)) --- E2((B))
        E1 - E2 --> E3["A - B"]
    end

    style U3 fill:#c8e6c9
    style I3 fill:#e1f5fe
    style E3 fill:#fff9c4

(2) UNION 与 UNION ALL

▶ 示例：UNION 合并三个季度的热销产品

SQL 
SELECT product_id, product_name FROM hot_products_q1
UNION
SELECT product_id, product_name FROM hot_products_q2
UNION
SELECT product_id, product_name FROM hot_products_q3
ORDER BY product_id;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

UNION 自动去重：如果某产品在 Q1 和 Q2 都热销，只出现一次。

▶ 示例：UNION ALL 保留重复

SQL 
SELECT product_id, product_name, 'Q1' AS quarter FROM hot_products_q1
UNION ALL
SELECT product_id, product_name, 'Q2' AS quarter FROM hot_products_q2
UNION ALL
SELECT product_id, product_name, 'Q3' AS quarter FROM hot_products_q3
ORDER BY quarter, product_id;
TEXT 📖 仅展示 
 product_id | product_name | quarter
------------+--------------+---------
        101 | Widget Pro   | Q1
        102 | Gadget Mini  | Q1
        101 | Widget Pro   | Q2
        103 | Server Rack  | Q2
        101 | Widget Pro   | Q3
        104 | Cable Max    | Q3
场景 推荐操作 原因
合并不同来源且无重复 UNION ALL 无需去重，更快
合并可能有重复且需去重 UNION 自动去重
合并并标记来源 UNION ALL + 标记列 保留重复并区分来源

▶ 示例：UNION ALL 合并多表统计

SQL 
SELECT 'NA' AS region, COUNT(*) AS order_count, SUM(amount) AS total FROM orders_na
UNION ALL
SELECT 'EU', COUNT(*), SUM(amount) FROM orders_eu
UNION ALL
SELECT 'APAC', COUNT(*), SUM(amount) FROM orders_apac;

输出：

TEXT 📖 仅展示 
 count 
-------
     5
(1 row)

(3) INTERSECT 与 INTERSECT ALL

▶ 示例：找三个季度都热销的常青产品

SQL 
SELECT product_id, product_name FROM hot_products_q1
INTERSECT
SELECT product_id, product_name FROM hot_products_q2
INTERSECT
SELECT product_id, product_name FROM hot_products_q3;
TEXT 📖 仅展示 
 product_id | product_name
------------+--------------
        101 | Widget Pro

Widget Pro 是唯一在每个季度都热销的常青款。

▶ 示例：INTERSECT ALL 保留重复计数

假设产品 101 在 Q1 热销列表中出现 2 次、Q2 出现 1 次、Q3 出现 3 次：

SQL 
SELECT product_id FROM hot_products_q1_detail
INTERSECT ALL
SELECT product_id FROM hot_products_q2_detail;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

INTERSECT ALL 返回 MIN(出现次数)：产品 101 返回 min(2, 1) = 1 行。

操作 去重 重复行处理 典型用途
INTERSECT 是 只保留一行 找共同项
INTERSECT ALL 否 按最小出现次数保留 精确匹配重复频率

▶ 示例：INTERSECT 找多表共同客户

SQL 
SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2023
INTERSECT
SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024
INTERSECT
SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025;

输出：

TEXT 📖 仅展示 
CREATE TABLE

连续三年都有下单的忠实客户。

(4) EXCEPT 与 EXCEPT ALL

▶ 示例：Q1 热销但 Q2 不再热销（流失款）

SQL 
SELECT product_id, product_name FROM hot_products_q1
EXCEPT
SELECT product_id, product_name FROM hot_products_q2;
TEXT 📖 仅展示 
 product_id | product_name
------------+--------------
        102 | Gadget Mini

Gadget Mini 在 Q1 热销但 Q2 没进热销榜——流失了。

▶ 示例：Q2 新晋热销产品

SQL 
SELECT product_id, product_name FROM hot_products_q2
EXCEPT
SELECT product_id, product_name FROM hot_products_q1;
TEXT 📖 仅展示 
 product_id | product_name
------------+--------------
        103 | Server Rack

Server Rack 在 Q2 新晋热销。

▶ 示例：EXCEPT ALL 保留重复计数

SQL 
SELECT product_id FROM order_items_2024
EXCEPT ALL
SELECT product_id FROM order_items_2025;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

如果产品 101 在 2024 出现 5 次、2025 出现 3 次，EXCEPT ALL 返回 5-3 = 2 行。

操作 去重 重复行处理 典型用途
EXCEPT 是 只保留一行 找差异项
EXCEPT ALL 否 按出现次数差保留 精确计算多余数量

4. Key Points

(1) 集合操作的规则

规则一：列数必须相同。

SQL 
SELECT id, name FROM table_a
UNION
SELECT id, name, price FROM table_b;
TEXT 📖 仅展示 
ERROR: each UNION query must have the same number of columns

规则二：对应列类型必须兼容。

规则 要求 错误后果
列数 必须相同 编译错误
列类型 必须兼容 隐式转换或报错
列名 取第一个查询的列名 需注意别名

▶ 示例：用别名统一列名

SQL 
SELECT product_id, product_name AS name FROM products_active
UNION ALL
SELECT sku AS product_id, title AS name FROM products_legacy
ORDER BY product_id;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ORDER BY 在集合操作中的位置

ORDER BY 只能出现在最后一个查询后面，作用于整个结果集。

▶ 示例：正确的 ORDER BY

SQL 
SELECT product_id, product_name FROM hot_products_q1
UNION ALL
SELECT product_id, product_name FROM hot_products_q2
ORDER BY product_id;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：用括号控制优先级

SQL 
(SELECT product_id FROM hot_products_q1
 EXCEPT
 SELECT product_id FROM hot_products_q2)
UNION ALL
(SELECT product_id FROM hot_products_q2
 EXCEPT
 SELECT product_id FROM hot_products_q3)
ORDER BY product_id;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

先算差集，再合并。不加括号时，UNION 的优先级低于 INTERSECT/EXCEPT。

操作 优先级 结合方向
INTERSECT 最高 从左到右
EXCEPT 中 从左到右
UNION / UNION ALL 中 从左到右

(3) NULL 在集合操作中的处理

集合操作将 NULL 视为相等（不同于普通比较中 NULL <> NULL）。

▶ 示例：UNION 中 NULL 去重

SQL 
SELECT NULL AS val
UNION
SELECT NULL AS val;
TEXT 📖 仅展示 
 val
-----

(1 row)

两个 NULL 被视为相同，UNION 去重后只保留一行。

▶ 示例：INTERSECT 中 NULL 匹配

SQL 
SELECT NULL AS val
INTERSECT
SELECT NULL AS val;
TEXT 📖 仅展示 
 val
-----

(1 row)

NULL 与 NULL 匹配，INTERSECT 返回一行。

场景 NULL 行为 与普通比较的区别
UNION 两个 NULL 视为相同，去重 普通 NULL = NULL 为 UNKNOWN
INTERSECT 两个 NULL 视为相同，匹配 普通 NULL = NULL 为 UNKNOWN
EXCEPT 两个 NULL 视为相同，抵消 普通 NULL <> NULL 为 UNKNOWN

(4) 集合操作 vs JOIN 对比

▶ 示例：INTERSECT 等价 INNER JOIN

SQL 
SELECT a.product_id
FROM hot_products_q1 a
INNER JOIN hot_products_q2 b ON a.product_id = b.product_id;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

等价于：

SQL 
SELECT product_id FROM hot_products_q1
INTERSECT
SELECT product_id FROM hot_products_q2;
维度 集合操作 JOIN
语义 行的集合运算 列的组合
输出列 取左侧列 两表列都可用
去重 UNION/INTERSECT/EXCEPT 自动去重 需手动 DISTINCT
NULL 匹配 NULL = NULL NULL <> NULL
性能 大数据量可能排序去重 索引 Hash Join 可能更快
适用场景 同结构结果集合并/交集/差集 异构表关联取列

5. Practice

▶ 示例：UNION ALL 合并订单与退款流水

SQL 
SELECT
  order_id   AS transaction_id,
  amount     AS credit,
  0          AS debit,
  'order'    AS type,
  created_at
FROM orders
UNION ALL
SELECT
  refund_id,
  0,
  refund_amount,
  'refund',
  created_at
FROM refunds
ORDER BY created_at;

输出：

TEXT 📖 仅展示 
CREATE TABLE

▶ 示例：EXCEPT 找已注册但未激活的用户

SQL 
SELECT user_id, email FROM registered_users
EXCEPT
SELECT user_id, email FROM activated_users;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：INTERSECT 找同时购买过 A 和 B 的客户

SQL 
SELECT customer_id FROM order_items WHERE product_id = 101
INTERSECT
SELECT customer_id FROM order_items WHERE product_id = 102;

输出：

TEXT 📖 仅展示 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：三年客户留存分析

SQL 
SELECT 'retained' AS status, COUNT(*) AS cnt FROM (
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024
  INTERSECT
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025
) t
UNION ALL
SELECT 'churned', COUNT(*) FROM (
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024
  EXCEPT
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025
) t;

输出：

TEXT 📖 仅展示 
 count 
-------
     5
(1 row)

▶ 示例：UNION ALL + GROUP BY 做趋势汇总

SQL 
SELECT
  product_id,
  SUM(CASE WHEN quarter = 'Q1' THEN 1 ELSE 0 END) AS q1_count,
  SUM(CASE WHEN quarter = 'Q2' THEN 1 ELSE 0 END) AS q2_count,
  SUM(CASE WHEN quarter = 'Q3' THEN 1 ELSE 0 END) AS q3_count
FROM (
  SELECT product_id, 'Q1' AS quarter FROM hot_products_q1
  UNION ALL
  SELECT product_id, 'Q2' FROM hot_products_q2
  UNION ALL
  SELECT product_id, 'Q3' FROM hot_products_q3
) combined
GROUP BY product_id
ORDER BY product_id;

输出：

TEXT 📖 仅展示 
 count 
-------
     5
(1 row)

6. Comprehensive Example

Bob 的跨季度热销产品分析——一次输出常青款、新晋款、流失款：

SQL 
WITH q1 AS (
  SELECT product_id, product_name FROM hot_products WHERE quarter = 'Q1'
),
q2 AS (
  SELECT product_id, product_name FROM hot_products WHERE quarter = 'Q2'
),
q3 AS (
  SELECT product_id, product_name FROM hot_products WHERE quarter = 'Q3'
),
evergreen AS (
  SELECT product_id, product_name, 'evergreen' AS trend FROM q1
  INTERSECT
  SELECT product_id, product_name, 'evergreen' FROM q2
  INTERSECT
  SELECT product_id, product_name, 'evergreen' FROM q3
),
new_q2 AS (
  SELECT product_id, product_name, 'new_in_q2' AS trend FROM q2
  EXCEPT
  SELECT product_id, product_name, 'new_in_q2' FROM q1
),
new_q3 AS (
  SELECT product_id, product_name, 'new_in_q3' AS trend FROM q3
  EXCEPT
  SELECT product_id, product_name, 'new_in_q3' FROM q2
),
churned_q2 AS (
  SELECT product_id, product_name, 'churned_in_q2' AS trend FROM q1
  EXCEPT
  SELECT product_id, product_name, 'churned_in_q2' FROM q2
),
churned_q3 AS (
  SELECT product_id, product_name, 'churned_in_q3' AS trend FROM q2
  EXCEPT
  SELECT product_id, product_name, 'churned_in_q3' FROM q3
)
SELECT * FROM evergreen
UNION ALL
SELECT * FROM new_q2
UNION ALL
SELECT * FROM new_q3
UNION ALL
SELECT * FROM churned_q2
UNION ALL
SELECT * FROM churned_q3
ORDER BY trend, product_id;
TEXT 📖 仅展示 
 product_id | product_name |    trend
------------+--------------+---------------
        101 | Widget Pro   | evergreen
        103 | Server Rack  | new_in_q2
        104 | Cable Max    | new_in_q3
        102 | Gadget Mini  | churned_in_q2
        103 | Server Rack  | churned_in_q3

7. 集合操作执行流程

100% 
flowchart TD
    A["Query A"] --> C{Operation}
    B["Query B"] --> C
    C -->|UNION| D["Combine + Deduplicate"]
    C -->|UNION ALL| E["Combine (keep duplicates)"]
    C -->|INTERSECT| F["Match + Deduplicate"]
    C -->|EXCEPT| G["A - B + Deduplicate"]
    D --> H["ORDER BY (optional)"]
    E --> H
    F --> H
    G --> H
    H --> I["Final Result"]

    style D fill:#c8e6c9
    style F fill:#e1f5fe
    style G fill:#fff9c4
步骤 操作 说明
1 执行各子查询 独立执行，结果集必须同结构
2 集合运算 UNION/INTERSECT/EXCEPT
3 去重（如需要） UNION/INTERSECT/EXCEPT 默认去重
4 ORDER BY 作用于最终结果集
5 LIMIT 限制最终输出行数

❓ 常见问题

Q UNION 和 UNION ALL 哪个更快？
A UNION ALL 更快，因为不需要去重。如果确定无重复或不需要去重，优先用 UNION ALL。
Q 集合操作的列名由谁决定？
A 取第一个查询的列名（或别名）。如需统一，在第一个查询中写好别名。
Q 集合操作能用在子查询中吗？
A 可以。SELECT * FROM (A UNION B) AS t 合法，需用括号包裹并加别名。
Q 多个集合操作的优先级是什么？
A INTERSECT > (EXCEPT = UNION)。INTERSECT 优先级最高，EXCEPT 和 UNION 同级。用括号明确优先级。
Q NULL 在集合操作中真的等于 NULL 吗？
A 是的。集合操作中两个 NULL 视为相等，这是 SQL 标准行为。与普通比较中 NULL = NULL 为 UNKNOWN 不同。
Q 什么时候用集合操作而不是 JOIN？
A 当只需要判断"存在/不存在"且不需要关联列时用集合操作；当需要两表列组合输出时用 JOIN。集合操作语义更直观，JOIN 更灵活。

📖 小节

📝 作业

  1. ⭐ 用 UNION ALL 合并 2024 年和 2025 年的订单表，添加年份标记列，按金额降序排列
  2. ⭐ 用 EXCEPT 找出在 customers 表中但不在 active_users 表中的用户
  3. ⭐⭐ 用 INTERSECT 找出在三个季度都出现过的热销产品 ID，并关联 products 表输出产品名
  4. ⭐⭐ 用 CTE + EXCEPT 实现客户流失分析：2024 年有订单但 2025 年无订单的客户列表
  5. ⭐⭐⭐ 写一条 SQL 综合使用 UNION ALL + INTERSECT + EXCEPT：输出一个报表包含三类产品（常青款/新晋款/流失款），每类带标记列，最后用 GROUP BY 统计每类产品数量
Web-Tutorial.com

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

100%

🙏 帮我们做得更好

我们是刚上线的编程教程站，几个人的小团队，精力有限。页面虽经检查，难免还有疏漏——链接失效、排版错乱、内容有误、语言生硬……

如果您发现了，麻烦告诉我们，我们会在收到反馈后第一时间进行修复，再次感谢您的光临 🙏