学习目标

学完本章后，你将能够：

• 使用 UNION 和 UNION ALL 组合多个查询结果
• 理解 INTERSECT 和 EXCEPT 操作的作用
• 掌握组合查询的应用场景和常见模式
• 了解组合查询中的排序和分页技巧
• 识别和避免组合查询中的常见错误

组合查询是 SQL 中将多个查询结果集合并为单个结果集的功能。

8.1 UNION 与 UNION ALL

UNION 和 UNION ALL 可组合两个或多个 SELECT 查询结果的运算符。

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并去除重复的行。

基本语法

▼
sql
复制代码
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

UNION 的规则

1. 每个 SELECT 语句必须有相同的列数
2. 对应的列必须有相似的数据类型
3. 每个 SELECT 语句中的列顺序必须相同
4. UNION 操作会自动去除重复行
5. 结果集中的列名通常取自第一个 SELECT 语句的列名

UNION 示例

假设我们有两个表：active_users 和 inactive_users，分别存储活跃和不活跃的用户数据。

active_users 表：

user_id	username	email
101	鱼皮	yupi@example.com
102	代码鸭	duck@example.com
103	算法达人	algo@example.com

inactive_users 表：

user_id	username	email
104	面试鸭	interview@example.com
105	老鱼	laoyu@example.com
103	算法达人	algo@example.com

使用 UNION 获取所有用户：

▼
sql
复制代码
SELECT user_id, username, email FROM active_users
UNION
SELECT user_id, username, email FROM inactive_users;

结果：

user_id	username	email
101	鱼皮	yupi@example.com
102	代码鸭	duck@example.com
103	算法达人	algo@example.com
104	面试鸭	interview@example.com
105	老鱼	laoyu@example.com

注意，重复的行（user_id = 103）只出现一次，因为 UNION 会去除重复项。

UNION ALL 操作符

UNION ALL 操作符也用于合并两个或多个 SELECT 语句的结果集，但不会去除重复行。

基本语法

▼
sql
复制代码
SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2;

UNION ALL 示例

使用上面的示例表，但改用 UNION ALL：

▼
sql
复制代码
SELECT user_id, username, email FROM active_users
UNION ALL
SELECT user_id, username, email FROM inactive_users;

结果：

user_id	username	email
101	鱼皮	yupi@example.com
102	代码鸭	duck@example.com
103	算法达人	algo@example.com
104	面试鸭	interview@example.com
105	老鱼	laoyu@example.com
103	算法达人	algo@example.com

这次，user_id = 103 的行出现了两次，因为 UNION ALL 不去除重复项。

UNION 与 UNION ALL 的区别

1. 去重处理：UNION 会去除重复行，而 UNION ALL 不会。
2. 性能：因为不需要检查重复项，UNION ALL 通常比 UNION 更快。
3. 应用场景：
   • 当确定结果集中没有重复行，或需要保留重复行时，使用 UNION ALL。
   • 当需要去除重复行时，使用 UNION。

组合多个查询

可以组合两个以上的 SELECT 语句：

▼
sql
复制代码
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2
UNION
SELECT column1, column2, ... FROM table3;

与 ORDER BY 结合使用

在组合查询中，ORDER BY 子句通常放在最后一个 SELECT 语句之后，它会对整个结果集进行排序：

▼
sql
复制代码
SELECT user_id, username, email FROM active_users
UNION
SELECT user_id, username, email FROM inactive_users
ORDER BY username;

这个查询会返回所有用户，并按用户名字母顺序排序。

8.2 组合查询的应用场景

组合查询在许多实际场景中非常有用。以下是一些常见的应用场景：

1. 合并多个表的数据

当数据分布在多个具有相似结构的表中时，可以使用组合查询将它们合并。

例如，合并不同类型的用户活动日志：

▼
sql
复制代码
SELECT 'comment' AS activity_type, user_id, created_at FROM user_comments
UNION ALL
SELECT 'like' AS activity_type, user_id, created_at FROM user_likes
UNION ALL
SELECT 'share' AS activity_type, user_id, created_at FROM user_shares
ORDER BY created_at DESC;

这个查询将评论、点赞和分享活动合并到一个时间线中。

2. 创建分类报表

组合查询可以用于创建包含不同数据分类的报表。

例如，按年龄段分组的用户统计：

▼
sql
复制代码
SELECT '18-24岁' AS age_group, COUNT(*) AS user_count 
FROM users WHERE age BETWEEN 18 AND 24
UNION
SELECT '25-34岁' AS age_group, COUNT(*) AS user_count 
FROM users WHERE age BETWEEN 25 AND 34
UNION
SELECT '35岁以上' AS age_group, COUNT(*) AS user_count 
FROM users WHERE age >= 35;

3. 处理垂直分区的数据

有时数据可能被垂直分区到多个表中。例如，用户的基本信息可能存储在一个表中，而其详细信息存储在另一个表中。

▼
sql
复制代码
SELECT user_id, username, email, NULL AS phone, NULL AS address 
FROM basic_user_info
UNION
SELECT user_id, NULL, NULL, phone, address 
FROM detailed_user_info;

4. 比较数据集

组合查询可用于比较不同时间段或不同条件下的数据：

▼
sql
复制代码
-- 比较今年与去年同期的销售
SELECT 'Today' AS period, COUNT(*) AS sales_count FROM orders WHERE order_date = CURRENT_DATE
UNION
SELECT 'Last Year' AS period, COUNT(*) AS sales_count FROM orders WHERE order_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);

5. 实现条件逻辑

在某些情况下，组合查询可以替代复杂的 CASE 表达式或条件逻辑：

▼
sql
复制代码
-- 根据不同的搜索条件查找用户
SELECT user_id, username FROM users WHERE search_type = 'username' AND username LIKE CONCAT('%', search_term, '%')
UNION
SELECT user_id, username FROM users WHERE search_type = 'email' AND email LIKE CONCAT('%', search_term, '%');

6. 数据迁移和转换

在数据迁移过程中，组合查询可用于从多个源表合并数据到目标表：

▼
sql
复制代码
INSERT INTO new_users_table (user_id, username, email, status)
SELECT user_id, username, email, 'active' FROM old_active_users
UNION
SELECT user_id, username, email, 'inactive' FROM old_inactive_users;

8.3 组合查询的注意事项

在使用组合查询时，需要注意以下几点：

1. 列数量和数据类型

组合查询中的每个 SELECT 语句必须具有相同数量的列，并且对应列的数据类型必须兼容。如果列数量不匹配或数据类型不兼容，查询将会失败。

▼
sql
复制代码
-- 错误示例：列数不匹配
SELECT user_id, username FROM users
UNION
SELECT product_id FROM products;

-- 正确示例：使用 NULL 或其他值填充缺少的列
SELECT user_id, username FROM users
UNION
SELECT product_id, NULL FROM products;

2. 列名和顺序

组合查询结果中的列名取自第一个 SELECT 语句。所有 SELECT 语句中的列顺序应该保持一致，以确保数据正确对应。

▼
sql
复制代码
-- 这两个查询的结果列名将是 user_id 和 name（而不是 username）
SELECT user_id, username AS name FROM users
UNION
SELECT customer_id, customer_name FROM customers;

3. ORDER BY 和 LIMIT 的位置

在组合查询中，全局的 ORDER BY 子句应放在最后一个 SELECT 语句之后，它会对整个结果集进行排序。

如果需要在组合之前对各个查询结果进行排序，可以在子查询中使用 ORDER BY：

▼
sql
复制代码
SELECT * FROM (
    SELECT user_id, username FROM users ORDER BY username LIMIT 10
) AS a
UNION
SELECT * FROM (
    SELECT customer_id, customer_name FROM customers ORDER BY customer_name LIMIT 10
) AS b;

4. 性能考虑

• UNION vs. UNION ALL：当不需要去重时，使用 UNION ALL 时，性能会更好一些。
• 避免不必要的排序：如果不需要排序，避免使用 ORDER BY，因为排序操作会消耗额外的资源。
• 优化子查询：确保每个子查询都经过优化，例如，使用适当的索引和限制返回的行数。

5. NULL 值的处理

在组合查询中，NULL 值的处理需要特别注意。在比较时，两个 NULL 值被视为相等，这可能会影响 UNION 的去重操作。

▼
sql
复制代码
-- 这个查询会将 NULL 值视为相等，并在结果中只显示一个 NULL
SELECT NULL AS value
UNION
SELECT NULL AS value;

6. 不同数据库系统的差异

不同的数据库系统对组合查询的支持可能有所不同：

• MySQL 支持 UNION 和 UNION ALL，但不直接支持 INTERSECT 和 EXCEPT。
• PostgreSQL、Oracle 和 SQL Server 支持 UNION、UNION ALL、INTERSECT 和 EXCEPT。
• SQLite 支持 UNION、UNION ALL、INTERSECT 和 EXCEPT，但语法可能有所不同。

在跨数据库系统使用组合查询时，需要了解这些差异，并根据需要调整查询语句。

练习题

练习 1：基本 UNION 应用

有一个 course_reviews 表记录课程评价，还有一个 blog_comments 表记录博客评论。请编写一个查询，合并这两个表中用户 ID 为 101 的所有评价和评论，结果应包含内容、创建时间和类型（'review' 或 'comment'）。

答案：

▼
sql
复制代码
SELECT 'review' AS type, review_text AS content, created_at
FROM course_reviews
WHERE user_id = 101
UNION
SELECT 'comment' AS type, comment_text AS content, created_at
FROM blog_comments
WHERE user_id = 101
ORDER BY created_at DESC;

练习 2：使用 UNION ALL 进行数据分析

假设有一个 user_activities 表记录了用户的各种活动，包含 activity_id, user_id, activity_type, activity_date 等字段。编写一个查询，统计每种活动类型在过去 30 天内的出现次数，以及总体的活动次数。

答案：

▼
sql
复制代码
SELECT activity_type, COUNT(*) AS activity_count
FROM user_activities
WHERE activity_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY activity_type
UNION ALL
SELECT 'Total' AS activity_type, COUNT(*) AS activity_count
FROM user_activities
WHERE activity_date >= CURRENT_DATE - INTERVAL 30 DAY;

练习 3：组合查询与条件逻辑

有一个表 interview_questions，包含不同类型的面试问题（Java、前端、数据库等）。编写一个查询，从这个表中查询最热门的 Java 问题和最热门的前端问题（假设热门程度由 views 字段表示），并在结果中标明类型。

答案：

▼
sql
复制代码
(SELECT 'Java' AS question_type, question_id, question_title, views
FROM interview_questions
WHERE category = 'Java'
ORDER BY views DESC
LIMIT 10)
UNION ALL
(SELECT '前端' AS question_type, question_id, question_title, views
FROM interview_questions
WHERE category = '前端'
ORDER BY views DESC
LIMIT 10)
ORDER BY question_type, views DESC;