PostgreSQL UNION 操作符

作者likuolei

PostgreSQL UNION 操作符完全指南

UNION 是集合操作的“拼接器”，将多个查询结果纵向合并，自动去重。
本文涵盖 UNION vs UNION ALL、语法、性能优化、排序、去重行为、与 JOIN 对比、复杂场景、递归、替代方案 等全部内容。

一、UNION vs UNION ALL 核心区别

操作符去重性能使用场景
UNIONYes 自动去重慢（需排序）需要唯一结果
UNION ALLNo 不去重快（直接拼接）允许重复，性能优先

黄金法则：能用 UNION ALL 就别用 UNION

二、基本语法

SELECT column1, column2 FROM table1
[WHERE ...]
UNION [ALL]
SELECT column1, column2 FROM table2
[WHERE ...]
[ORDER BY ...]
[LIMIT ...];

要求

  1. 列数相同
  2. 列类型兼容（可隐式转换）
  3. 列名以第一个查询为准

三、准备测试数据

-- 2025 年销售表
CREATE TABLE sales_2025 (
    id BIGSERIAL,
    product TEXT,
    amount NUMERIC(10,2),
    sale_date DATE
);

-- 2024 年销售表
CREATE TABLE sales_2024 (
    id BIGSERIAL,
    product TEXT,
    amount NUMERIC(10,2),
    sale_date DATE
);

-- 插入数据（含重复）
INSERT INTO sales_2025 VALUES
(1, 'iPhone', 1200, '2025-01-15'),
(2, 'MacBook', 2500, '2025-01-17'),
(3, 'iPhone', 1200, '2025-01-20');

INSERT INTO sales_2024 VALUES
(1, 'iPhone', 1200, '2024-12-10'),
(2, 'AirPods', 250, '2024-12-15'),
(3, 'iPhone', 1200, '2024-12-20');

四、基础用法

1. UNION ALL：直接拼接（推荐）

SELECT product, amount, sale_date FROM sales_2025
UNION ALL
SELECT product, amount, sale_date FROM sales_2024
ORDER BY sale_date DESC;

输出（6 行）：

 product  | amount | sale_date
----------+--------+------------
 iPhone   |   1200 | 2025-01-20
 MacBook  |   2500 | 2025-01-17
 iPhone   |   1200 | 2025-01-15
 iPhone   |   1200 | 2024-12-20
 AirPods  |    250 | 2024-12-15
 iPhone   |   1200 | 2024-12-10

2. UNION：自动去重

SELECT product, amount FROM sales_2025
UNION
SELECT product, amount FROM sales_2024;

输出（4 行）：

 product  | amount
----------+--------
 AirPods  |    250
 iPhone   |   1200
 MacBook  |   2500

五、UNION 与 ORDER BY / LIMIT

ORDER BY 只能放在最后，作用于整个结果集

(SELECT product, amount FROM sales_2025 WHERE amount > 2000)
UNION ALL
(SELECT product, amount FROM sales_2024 WHERE amount > 2000)
ORDER BY amount DESC
LIMIT 3;

技巧：为子查询加别名排序

(SELECT '2025' AS year, product, amount FROM sales_2025)
UNION ALL
(SELECT '2024', product, amount FROM sales_2024)
ORDER BY year DESC, amount DESC;

六、性能优化

优化点建议
优先 UNION ALL避免排序去重
子查询加索引加速过滤
列类型一致避免隐式转换
避免 UNION + ORDER BY 无索引建排序索引
-- 为 WHERE 列建索引
CREATE INDEX idx_sales_2025_amount ON sales_2025(amount);
CREATE INDEX idx_sales_2024_amount ON sales_2024(amount);

七、UNION vs JOIN：何时用哪个？

场景推荐
纵向合并同结构表UNION ALL
横向关联不同表JOIN
合并后去重UNION
合并后统计UNION ALL + GROUP BY
-- 错误：用 JOIN 合并年份
SELECT s25.product, s24.amount FROM sales_2025 s25 JOIN sales_2024 s24 ON ...

-- 正确：用 UNION ALL
SELECT product, amount FROM sales_2025
UNION ALL
SELECT product, amount FROM sales_2024

八、复杂场景：多表 + 条件 + 聚合

-- 2025 高价值订单 + 2024 VIP 客户订单
(SELECT 'High-Value' AS type, product, amount FROM sales_2025 WHERE amount > 2000)
UNION ALL
(SELECT 'VIP', product, amount FROM sales_2024 WHERE customer_type = 'VIP')
ORDER BY amount DESC;

九、递归 UNION（替代递归 CTE）

-- 简单层级展开（不推荐，CTE 更清晰）
WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.level + 1
    FROM employees e JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

推荐：用 WITH RECURSIVE，更清晰

十、UNION + NULL 填充（补全列）

-- 合并两表，补全缺失列
SELECT id, product, amount, NULL AS region FROM sales_2025
UNION ALL
SELECT id, product, amount, region FROM sales_international

十一、替代方案

需求替代方式
合并分区表TABLE sales_2025 UNION ALL TABLE sales_2024
动态合并多表视图 + UNION ALL
去重后统计UNION ALL + GROUP BY + HAVING
-- 视图方式
CREATE VIEW all_sales AS
SELECT * FROM sales_2025
UNION ALL
SELECT * FROM sales_2024;

-- 使用
SELECT product, SUM(amount) FROM all_sales GROUP BY product;

十二、常见陷阱与解决方案

陷阱说明解决方案
UNION 自动去重慢排序开销用 UNION ALL
列类型不匹配隐式转换显式 CAST
ORDER BY 放错位置语法错误放最后
LIMIT 只作用于一个子查询结果不完整放 UNION 后
列名不一致以第一个为准用别名统一
-- 错误
(SELECT product FROM sales_2025 ORDER BY amount)
UNION ALL
(SELECT product FROM sales_2024);

-- 正确
(SELECT product FROM sales_2025)
UNION ALL
(SELECT product FROM sales_2024)
ORDER BY product;

十三、最佳实践 Checklist

项目建议
默认用 UNION ALL性能优先
UNION 仅在需要去重时明确意图
列类型、顺序一致避免转换
ORDER BY 放最后作用全结果
子查询加索引加速过滤
用视图封装多表合并可维护
EXPLAIN 验证计划确保无排序

十四、一键年度销售报表

-- 合并两年数据 + 按月统计
WITH monthly_sales AS (
    SELECT 
        EXTRACT(YEAR FROM sale_date)::INT AS year,
        EXTRACT(MONTH FROM sale_date)::INT AS month,
        SUM(amount) AS total
    FROM (
        SELECT amount, sale_date FROM sales_2025
        UNION ALL
        SELECT amount, sale_date FROM sales_2024
    ) all_sales
    GROUP BY year, month
)
SELECT 
    year,
    month,
    total,
    LAG(total) OVER (ORDER BY year, month) AS prev_month,
    ROUND(
        (total - LAG(total) OVER (ORDER BY year, month)) / 
        NULLIF(LAG(total) OVER (ORDER BY year, month), 0) * 100,
        2
    ) AS growth_pct
FROM monthly_sales
ORDER BY year, month;

十五、快速实战：5 分钟掌握 UNION

-- 1. UNION ALL 合并
SELECT product, amount FROM sales_2025
UNION ALL
SELECT product, amount FROM sales_2024;

-- 2. UNION 去重
SELECT product, amount FROM sales_2025
UNION
SELECT product, amount FROM sales_2024;

-- 3. 带排序和限制
(SELECT '2025' AS year, product FROM sales_2025 WHERE amount > 2000)
UNION ALL
(SELECT '2024', product FROM sales_2024 WHERE amount > 2000)
ORDER BY year DESC, product
LIMIT 5;

-- 4. 创建合并视图
CREATE VIEW v_all_sales AS
SELECT * FROM sales_2025
UNION ALL
SELECT * FROM sales_2024;

现在就动手：
在测试表中：

  1. 用 UNION ALL 合并 sales_2025 和 sales_2024
  2. 用 UNION 去重 (product, amount)
  3. 查询 2025 高价值订单 + 2024 iPhone 订单，按金额降序
  4. 创建视图 v_all_sales，统计总销售额

十六、性能对比（EXPLAIN）

-- 慢：UNION + 去重
EXPLAIN ANALYZE 
SELECT product FROM sales_2025
UNION
SELECT product FROM sales_2024;

-- 快：UNION ALL
EXPLAIN ANALYZE 
SELECT product FROM sales_2025
UNION ALL
SELECT product FROM sales_2024;

需要我生成：

  • 100万条分表数据 + UNION 压力测试？回复 测试数据
  • 动态 UNION 多表（Python）？回复 Python UNION
  • 分区表 vs UNION 性能对比？回复 分区对比
  • EXPLAIN 可视化分析？回复 EXPLAIN 分析

随时告诉我！

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注