SQL UNION 集合操作

集合操作允许将多个查询结果合并为一个结果集。本章介绍 UNION、INTERSECT 和 EXCEPT 操作。

UNION 合并结果

UNION 用于合并两个或多个 SELECT 语句的结果集。

基本语法

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

基本示例

-- 合并两个表的用户
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

-- 合并不同城市
SELECT city FROM users
UNION
SELECT city FROM employees;

UNION 规则

使用 UNION 时需遵守以下规则：

1. 列数必须相同：每个 SELECT 语句必须有相同数量的列
2. 列类型必须兼容：对应列的数据类型必须兼容
3. 列名取自第一个查询：结果集的列名来自第一个 SELECT 语句
4. 默认去重：UNION 默认去除重复行

-- 正确：列数相同
SELECT id, name FROM users
UNION
SELECT id, name FROM employees;

-- 错误：列数不同
SELECT id, name FROM users
UNION
SELECT id, name, email FROM employees;  -- Error

-- 正确：类型兼容
SELECT id, name FROM users
UNION
SELECT id, age FROM employees;  -- name 和 age 都是字符串或数字

UNION ALL 保留重复

UNION ALL 保留所有行，包括重复行：

-- UNION：去除重复
SELECT city FROM users
UNION
SELECT city FROM employees;
-- 结果：每个城市只出现一次

-- UNION ALL：保留重复
SELECT city FROM users
UNION ALL
SELECT city FROM employees;
-- 结果：每个城市可能出现多次

性能对比

-- UNION ALL 更快（不需要去重）
SELECT id FROM large_table1
UNION ALL
SELECT id FROM large_table2;

-- UNION 需要排序去重，较慢
SELECT id FROM large_table1
UNION
SELECT id FROM large_table2;

提示

如果确定没有重复数据，使用 UNION ALL 性能更好。

INTERSECT 交集

INTERSECT 返回两个查询结果中都存在的行：

-- 查找同时在两个表中的城市
SELECT city FROM users
INTERSECT
SELECT city FROM employees;

-- 查找共同客户
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

MySQL 不支持 INTERSECT

MySQL 不直接支持 INTERSECT，可以使用以下替代方案：

-- 方法1：使用 INNER JOIN
SELECT DISTINCT u.city
FROM users u
INNER JOIN employees e ON u.city = e.city;

-- 方法2：使用 IN
SELECT DISTINCT city FROM users
WHERE city IN (SELECT city FROM employees);

-- 方法3：使用 EXISTS
SELECT DISTINCT city FROM users u
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.city = u.city);

EXCEPT 差集

EXCEPT（MySQL 使用 MINUS）返回在第一个查询中存在但在第二个查询中不存在的行：

-- PostgreSQL / SQL Server
SELECT city FROM users
EXCEPT
SELECT city FROM employees;

-- Oracle
SELECT city FROM users
MINUS
SELECT city FROM employees;

MySQL 不支持 EXCEPT

MySQL 不直接支持 EXCEPT，可以使用以下替代方案：

-- 方法1：使用 LEFT JOIN
SELECT DISTINCT u.city
FROM users u
LEFT JOIN employees e ON u.city = e.city
WHERE e.city IS NULL;

-- 方法2：使用 NOT IN
SELECT DISTINCT city FROM users
WHERE city NOT IN (SELECT city FROM employees);

-- 方法3：使用 NOT EXISTS
SELECT DISTINCT city FROM users u
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.city = u.city);

ORDER BY 排序

集合操作的结果可以使用 ORDER BY 排序：

-- ORDER BY 必须放在最后
SELECT name FROM users
UNION
SELECT name FROM employees
ORDER BY name;

-- 使用列别名排序
SELECT name AS full_name FROM users
UNION
SELECT name AS full_name FROM employees
ORDER BY full_name;

-- 使用列位置排序
SELECT name FROM users
UNION
SELECT name FROM employees
ORDER BY 1;

实际应用示例

合并多个数据源

-- 合并不同渠道的用户
SELECT 'web' AS source, user_id, created_at FROM web_users
UNION ALL
SELECT 'mobile' AS source, user_id, created_at FROM mobile_users
UNION ALL
SELECT 'api' AS source, user_id, created_at FROM api_users
ORDER BY created_at DESC;

查找活跃用户

-- 最近30天有登录或下单的用户
SELECT DISTINCT user_id FROM (
    SELECT user_id FROM logins WHERE login_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    UNION
    SELECT user_id FROM orders WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
) active_users;

对比数据差异

-- 找出只在表A中存在的记录
SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;

-- 找出只在表B中存在的记录
SELECT id FROM table_b
EXCEPT
SELECT id FROM table_a;

合并历史数据

-- 合并多个年份的销售数据
SELECT '2021' AS year, product_id, SUM(amount) AS total
FROM sales_2021
GROUP BY product_id
UNION ALL
SELECT '2022' AS year, product_id, SUM(amount) AS total
FROM sales_2022
GROUP BY product_id
UNION ALL
SELECT '2023' AS year, product_id, SUM(amount) AS total
FROM sales_2023
GROUP BY product_id;

集合操作对比

操作	说明	是否去重
UNION	合并结果	是
UNION ALL	合并结果	否
INTERSECT	交集	是
EXCEPT/MINUS	差集	是

各数据库支持情况

数据库	UNION	UNION ALL	INTERSECT	EXCEPT
MySQL	✓	✓	✗	✗
PostgreSQL	✓	✓	✓	✓
Oracle	✓	✓	✓	MINUS
SQL Server	✓	✓	✓	✓
SQLite	✓	✓	✓	✓

小结

本章我们学习了：

1. UNION 合并结果集（去重）
2. UNION ALL 合并结果集（不去重）
3. INTERSECT 取交集
4. EXCEPT/MINUS 取差集
5. ORDER BY 排序集合结果
6. 各数据库的差异

练习

1. 合并 customers 和 suppliers 表的所有城市（去重）
2. 找出同时在 2022 年和 2023 年都有订单的客户
3. 找出在 users 表中但不在 employees 表中的邮箱
4. 合并三个季度的销售数据并按日期排序