SQL表集合运算学习

第8章表的集合运算 – 学习笔记

**学习目标**：掌握SQL集合运算概念和应用，了解不同数据库系统差异

1. 集合运算概述

1.1 基本概念

集合运算对两个或多个查询结果集进行数学集合操作。表可看作由行组成的集合，支持基于行的各种集合运算。

1.2 三种运算符

运算符	含义	SQLite支持
INTERSECT	交集	✅ 支持
UNION	并集	✅ 支持
EXCEPT	差集	✅ 支持

1.3 执行条件

• 字段数量一样：两个查询返回字段个数相等

• 字段类型兼容：SQLite动态类型，要求较宽松

数据库差异

数据库	类型检查	错误处理
SQLite	宽松	相对宽松
MySQL	严格	严格的类型检查
PostgreSQL	最严格	最严格的类型检查
SQL Server	严格	中等严格度
Oracle	严格	详细的错误信息

1.4 测试环境

— 测试表
CREATE TABLE t_set1 (id INTEGER, name VARCHAR(10));
CREATE TABLE t_set2 (id INTEGER, name VARCHAR(10));
— 数据
INSERT INTO t_set1 VALUES (1, 'apple'), (2, 'banana'), (3, 'orange');
INSERT INTO t_set2 VALUES (1, 'apple'), (2, 'banana'), (4, 'pear');

──────────────────────────────────────────────────

2. 交集运算 (INTERSECT)

2.1 概念说明

交集运算返回同时出目前两个查询结果聚焦的数据。

图解：

集合A: {apple, banana, orange}
集合B: {apple, banana, pear}
交集A ∩ B: {apple, banana}

2.2 SQLite实现

— 交集运算
SELECT id, name FROM t_set1
INTERSECT
SELECT id, name FROM t_set2;

结果：

id | name
—|——–
1 | apple
2 | banana

2.3 等价连接查询

— 使用INNER JOIN
SELECT DISTINCT t1.id, t1.name
FROM t_set1 t1
INNER JOIN t_set2 t2
ON t1.id = t2.id AND t1.name = t2.name;

数据库差异

数据库	支持程度	ALL选项	替代方案
SQLite	✅ 完整支持	❌ 不支持	INNER JOIN
MySQL	❌ 不支持	❌ 不支持	INNER JOIN
PostgreSQL	✅ 完整支持	✅ 支持	功能完整
SQL Server	✅ 完整支持	❌ 不支持	标准语法
Oracle	✅ 完整支持	✅ 21c+支持	企业级功能

MySQL替代方案

— MySQL中INTERSECT的替代
SELECT DISTINCT t1.id, t1.name
FROM t_set1 t1
INNER JOIN t_set2 t2
ON t1.id = t2.id AND t1.name = t2.name;

──────────────────────────────────────────────────

3. 并集运算 (UNION)

3.1 概念说明

并集运算返回出目前任一查询结果聚焦的数据。

图解：

集合A: {apple, banana, orange}
集合B: {apple, banana, pear}
并集A ∪ B: {apple, banana, orange, pear}

3.2 基本语法

SELECT column1, column2, … FROM table1
UNION [ALL|DISTINCT]
SELECT column1, column2, … FROM table2;

• DISTINCT（默认）：去除重复记录

• ALL：保留所有记录

3.3 SQLite示例

— 去重并集
SELECT id, name FROM t_set1
UNION
SELECT id, name FROM t_set2;
— 保留重复
SELECT id, name FROM t_set1
UNION ALL
SELECT id, name FROM t_set2;

3.4 性能对比

• UNION：需要去重操作，性能较低

• UNION ALL：直接合并，性能更高

** 提议**：确定无重复数据时，优先使用`UNION ALL`

数据库差异

数据库	支持程度	性能特点	优化策略
SQLite	✅ 完全支持	简单高效	基本优化
MySQL	✅ 完全支持	优秀性能	强劲的查询优化器
PostgreSQL	✅ 完全支持	最佳性能	先进的优化算法
SQL Server	✅ 完全支持	良好性能	企业级优化
Oracle	✅ 完全支持	企业级性能	成熟的成本优化

──────────────────────────────────────────────────

4. 差集运算 (EXCEPT)

4.1 概念说明

差集运算返回出目前第一个结果集但不在第二个结果聚焦的数据。

图解：

集合A: {apple, banana, orange}
集合B: {apple, banana, pear}
差集A – B: {orange}

4.2 SQLite实现

— 差集运算
SELECT id, name FROM t_set1
EXCEPT
SELECT id, name FROM t_set2;

结果：

id | name
—|——–
3 | orange

4.3 等价连接查询

— 使用LEFT JOIN
SELECT t1.id, t1.name
FROM t_set1 t1
LEFT JOIN t_set2 t2
ON t1.id = t2.id AND t1.name = t2.name
WHERE t2.id IS NULL;

数据库差异

数据库	关键字	支持程度	替代方案
SQLite	EXCEPT	✅ 完整支持	LEFT JOIN
MySQL	不支持	❌ 完全不支持	LEFT JOIN/NOT IN
PostgreSQL	EXCEPT	✅ 完整支持	功能完整
SQL Server	EXCEPT	✅ 完整支持	标准语法
Oracle	MINUS/EXCEPT	✅ 完整支持	传统使用MINUS

MySQL替代方案

— MySQL中EXCEPT的替代写法
— 方案1：LEFT JOIN
SELECT t1.id, t1.name
FROM t_set1 t1
LEFT JOIN t_set2 t2
ON t1.id = t2.id AND t1.name = t2.name
WHERE t2.id IS NULL;
— 方案2：NOT IN
SELECT id, name FROM t_set1
WHERE (id, name) NOT IN (
SELECT id, name FROM t_set2
);

Oracle特殊语法

— Oracle传统使用MINUS
SELECT id, name FROM table1
MINUS
SELECT id, name FROM table2;
— Oracle 21c后支持EXCEPT
SELECT id, name FROM table1
EXCEPT
SELECT id, name FROM table2;

──────────────────────────────────────────────────

5. 集合运算与排序

5.1 重大规则

错误方式：

— 错误！不能在集合运算符前使用ORDER BY
SELECT id, name FROM t_set1 ORDER BY id
UNION ALL
SELECT id, name FROM t_set2;

✅ 正确方式：

— 正确！ORDER BY必须放在最后
SELECT id, name FROM t_set1
UNION ALL
SELECT id, name FROM t_set2
ORDER BY id;

5.2 注意事项

• 位置要求：ORDER BY必须在集合运算的最后

• 字段引用：使用第一个查询中的字段名

• 性能思考：排序在数据合并后执行

数据库差异

数据库	ORDER BY位置	字段引用	别名支持
SQLite	必须在最后	字段名/位置	❌ 有限支持
MySQL	必须在最后	字段名/位置	✅ 支持列别名
PostgreSQL	必须在最后	字段名/位置	✅ 完全支持
SQL Server	必须在最后	字段名/位置	✅ 支持列别名
Oracle	必须在最后	字段名/位置	✅ 支持列别名

──────────────────────────────────────────────────

6. 运算符优先级

6.1 SQLite优先级规则

SQLite中，所有集合运算符优先级一样，从左到右执行。

6.2 示例分析

— SQLite执行顺序：先UNION ALL，再INTERSECT
SELECT 1 AS n
UNION ALL
SELECT 1
INTERSECT
SELECT 1;
— 结果：1个结果

6.3 数据库对比

数据库	优先级规则	示例结果	括号支持
SQLite	一样优先级	1个结果	❌ 不支持
PostgreSQL	INTERSECT优先	2个结果	✅ 支持
SQL Server	INTERSECT优先	2个结果	✅ 支持
Oracle	INTERSECT优先	2个结果	✅ 支持
MySQL	（无INTERSECT）	不适用	✅ 支持UNION括号

**⚠️ 重大**：SQLite不支持括号修改优先级，只能从左到右执行。

──────────────────────────────────────────────────

7. 案例分析

7.1 案例一：优秀员工分析

需求：找出2020年新晋的优秀员工。

SQLite解决方案

— 使用EXCEPT
SELECT emp_id, emp_name FROM excellent_emp
WHERE year = 2020
EXCEPT
SELECT emp_id, emp_name FROM excellent_emp
WHERE year = 2019;

通用替代方案

— 使用LEFT JOIN（兼容所有数据库）
SELECT t1.emp_id, t1.emp_name
FROM excellent_emp t1
LEFT JOIN excellent_emp t2
ON t1.emp_id = t2.emp_id AND t2.year = 2019
WHERE t1.year = 2020 AND t2.emp_id IS NULL;

7.2 案例二：用户权限管理

需求：查询用户所有权限（角色权限+直接权限）。

SQLite实现

— 使用UNION合并权限
SELECT p.permission_name,
CASE WHEN up.user_id IS NOT NULL THEN 'Y' ELSE 'N' END AS direct_permission
FROM t_permission p
WHERE p.permission_id IN (
— 角色权限
SELECT rp.permission_id
FROM t_role_permission rp
JOIN t_user_role ur ON rp.role_id = ur.role_id
WHERE ur.user_id = 2

UNION

— 直接权限
SELECT permission_id
FROM t_user_permission
WHERE user_id = 2
)
ORDER BY p.sort_order;

PostgreSQL优化版本

— 使用CTE提高可读性
WITH user_permissions AS (
SELECT permission_id FROM t_user_permission WHERE user_id = 2
UNION
SELECT rp.permission_id
FROM t_role_permission rp
JOIN t_user_role ur ON rp.role_id = ur.role_id WHERE ur.user_id = 2
)
SELECT p.permission_name,
CASE WHEN up.permission_id IS NOT NULL THEN 'Y' ELSE 'N' END as direct_flag
FROM t_permission p
JOIN user_permissions up ON p.permission_id = up.permission_id
ORDER BY p.sort_order;

──────────────────────────────────────────────────

8. 数据库兼容性总结

8.1 功能支持矩阵

功能特性	SQLite	MySQL	PostgreSQL	SQL Server	Oracle
UNION	✅	✅	✅	✅	✅
INTERSECT	✅	❌	✅	✅	✅
EXCEPT	✅	❌	✅	✅	✅
UNION ALL	✅	✅	✅	✅	✅
INTERSECT ALL	❌	❌	✅	❌	✅ 21c+
EXCEPT ALL	❌	❌	✅	❌	✅ 21c+
运算符优先级	一样	N/A	INTERSECT优先	INTERSECT优先	INTERSECT优先
括号控制	❌	✅ UNION支持	✅	✅	✅

8.2 语法差异速查

交集运算

— SQLite / PostgreSQL / SQL Server
SELECT * FROM table1 INTERSECT SELECT * FROM table2;
— Oracle
SELECT * FROM table1 MINUS SELECT * FROM table2;
— MySQL (替代)
SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;

差集运算

— SQLite / PostgreSQL / SQL Server / Oracle
SELECT * FROM table1 EXCEPT SELECT * FROM table2;
— MySQL (替代)
SELECT t1.* FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

8.3 跨数据库兼容策略

策略1：使用通用写法

— 用LEFT JOIN替代集合运算（通用性强）
SELECT t1.* FROM table1 t1
LEFT JOIN table2 t2 ON 联接条件
WHERE t2.主键 IS NULL; — 模拟EXCEPT
WHERE t2.主键 IS NOT NULL; — 模拟INTERSECT

策略2：选择合适数据库

• SQLite：嵌入式应用、移动应用、学习开发

• MySQL：Web应用、中小型企业应用

• PostgreSQL：复杂数据分析、需要高级功能

• SQL Server：Windows平台企业应用

• Oracle：大型企业、高性能需求系统

8.4 性能对比

场景	SQLite	MySQL	PostgreSQL	SQL Server	Oracle
小数据集	优秀	优秀	优秀	优秀	优秀
大数据集	一般	优秀	卓越	优秀	卓越
复杂集合运算	一般	不适用	卓越	优秀	卓越
查询优化	基础	优秀	卓越	优秀	卓越