深入理解 SQL 组合查询：UNION、INTERSECT、EXCEPT

一、引言

在日常开发中，我们经常需要从多个表或多个查询中整合数据。SQL 中的组合查询（Combination Query）提供了一种灵活且强大的方式，帮助我们高效地处理这些需求。本文将详细讲解 SQL 中常见的组合查询，包括 UNION、UNION ALL、INTERSECT 和 EXCEPT，并通过实例帮助读者掌握其使用方法。

1. 什么是组合查询？

组合查询是一种将多个查询结果集合并到一起的技术。它可以用于合并数据、过滤数据或灵活地构建复杂查询。

2. 使用场景

• 数据整合：将来自不同表的结果合并在一起。
• 数据过滤：根据条件筛选交集或差集的数据。
• 灵活查询：简化复杂查询逻辑，提高代码可读性。

二、组合查询的基本语法

1. UNION

UNION 用于合并两个查询结果集，同时去除重复数据。

语法：

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

示例：

-- 查询来自两个表的用户姓名
SELECT name FROM users_2023
UNION
SELECT name FROM users_2024;

结果：两个表的用户名合并后，无重复值。

2. UNION ALL

UNION ALL 与 UNION 类似，但不会去除重复数据。

语法：

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

示例：

-- 查询两个表的所有用户名，包括重复值
SELECT name FROM users_2023
UNION ALL
SELECT name FROM users_2024;

结果：保留重复数据，性能优于 UNION。

3. INTERSECT

INTERSECT 用于获取两个查询结果集的交集。

语法：

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

示例：

-- 查询两个表中共同的用户名
SELECT name FROM users_2023
INTERSECT
SELECT name FROM users_2024;

结果：只包含同时存在于两个表中的用户名。

4. EXCEPT

EXCEPT 用于获取第一个查询结果集中存在但第二个结果集中不存在的数据（差集）。

语法：

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

示例：

-- 查询存在于 users_2023 表中但不存在于 users_2024 表中的用户名
SELECT name FROM users_2023
EXCEPT
SELECT name FROM users_2024;

结果：仅包含在第一个表中存在的数据。

注意：INTERSECT 和 EXCEPT 并非所有数据库都支持，使用前需检查数据库兼容性。

三、组合查询的使用要点

1. 列数与列类型一致

组合查询的所有子查询必须选择相同数量的列，且列的数据类型需要兼容。

示例：

-- 错误示例：列数不一致
SELECT name FROM users_2023
UNION
SELECT name, email FROM users_2024;

-- 正确示例：
SELECT name, 'N/A' AS email FROM users_2023
UNION
SELECT name, email FROM users_2024;

2. 排序与分页

组合查询结果可以通过 ORDER BY 排序，也可以使用 LIMIT 或 OFFSET 实现分页。

示例：

-- 合并两个表的用户名，并按字母排序
SELECT name FROM users_2023
UNION
SELECT name FROM users_2024
ORDER BY name ASC;

3. NULL 值的处理

组合查询中的 NULL 值会按数据库的默认规则处理，需注意结果中的空值。

四、性能优化

1. 优先使用 UNION ALL

当确定结果集中不需要去重时，使用 UNION ALL 替代 UNION 可以提升性能。

示例：

-- 性能优化：使用 UNION ALL 避免去重开销
SELECT name FROM users_2023
UNION ALL
SELECT name FROM users_2024;

2. 优化子查询

确保子查询的条件过滤、索引使用合理，减少中间结果集的大小。

示例：

-- 在子查询中添加过滤条件
SELECT name FROM users_2023 WHERE active = 1
UNION
SELECT name FROM users_2024 WHERE active = 1;

五、实践案例

案例一：合并用户数据

需求：从两张用户表中合并所有用户数据，并去除重复。

示例代码：

SELECT user_id, name, email FROM users_2023
UNION
SELECT user_id, name, email FROM users_2024;

案例二：获取交集数据

需求：找出两张订单表中共同的商品 ID。

示例代码：

SELECT product_id FROM orders_2023
INTERSECT
SELECT product_id FROM orders_2024;

案例三：计算差集

需求：找出存在于所有商品表中但未出售的商品。

示例代码：

SELECT product_id FROM products_all
EXCEPT
SELECT product_id FROM products_sold;

结语

组合查询是 SQL 中的重要工具，能高效地整合和处理多个结果集。

熟练掌握 UNION、UNION ALL、INTERSECT 和 EXCEPT 的语法和使用场景，有助于构建更灵活的查询逻辑。

优化性能 是实际应用中的关键，可以通过减少去重操作和优化子查询实现更高效的查询。