包括并集（Union）、交集（Intersect）和差集（Except）。执行SQL集合运算时，集合操作中的两个查询结果需要满足以下条件：

• 两个查询结果集中字段的数量必须相同。
• 两个查询结果集中对应字段的类型必须匹配或兼容。

注意

• SQLite 使用动态数据类型，不要求字段类型匹配或兼容。
• 某些数据库（例如 MySQL）可能会尝试执行隐式的类型转换。

交集求同

sql
SELECT column1, column2, ...
FROM table1
INTERSECT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;

• DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
• ALL 表示保留合并结果中的重复记录。
• 默认值为 DISTINCT。
• mysql 不支持 INTERSECT。
• pg 完整支持 DISTINCT 和 ALL。
• oracle 21c 开始支持 ALL 选项。
• 其他数据库支持简写的 INTERSECT。

sql
-- oracle mss pg sqlite
SELECT id, name
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;

交集运算通常可以改写为内连接。

• mysql 可以用这种方法实现交集。

sql
SELECT DISTINCT t1.id, t1.name
FROM t_set1 t1
JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)

并集存异

sql
SELECT column1, column2, ...
FROM table1
UNION [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;

• DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
• ALL 表示保留合并结果中的重复记录。
• 默认值为 DISTINCT。

sql
SELECT id, name
FROM t_set1
UNION
SELECT id, name
FROM table2;

并集运算可以改写为全外连接。

sql
-- oracle mss pg
-- COALESCE 返回第一个非空值
SELECT COALESCE(t1.id, t2.id), COALESCE(t1.name, t2.name)
FROM t_set1 t1
FULL JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name);

• mysql sqlite 不支持全外连接。
• mysql sqlite 会进行隐式数据类型转换。

sql
-- mysql sqlite
-- mysql 将第一个查询返回的字段转换为字符串类型
-- sqlite 将第二个查询返回的字段转换为整数类型
SELECT 1 AS id
UNION ALL
SELECT 'sql' AS name;

差集排他

sql
SELECT column1, column2, ...
FROM table1
EXCEPT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;

• DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
• ALL 表示保留合并结果中的重复记录。
• 默认值为 DISTINCT。
• mysql 不支持 EXCEPT 运算符。
• oracle 21c 开始支持 EXCEPT 关键字，其以前的版本使用等价的 MINUS 运算符。
• oracle 21c 开始支持 ALL 选项。
• 只有 pg 支持完整的 DISTINCT 和 ALL 选项。
• 其他数据库支持简写的EXCEPT。

sql
-- oracle 19c以及更早
SELECT id, name
FROM t_set1
MINUS
SELECT id, name
FROM t_set2;

差集运算可以改写为左外连接或右外连接。

• mysql 可以用这种方式实现差集。

sql
SELECT t1.id, t1.name
FROM t_set1 t1
LEFT JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)
WHERE t2.id IS NULL;

集合运算与排序

想要对集合运算的结果进行排序操作，必须将 ORDER BY 子句写在整个查询语句的最后，集合运算符之前的 SELECT 语句中不能出现排序子句。

sql
SELECT id, name
FROM t_set1
UNION ALL
SELECT id, name
FROM t_set2
ORDER BY id;

运算符的优先级

• 按照SQL标准，交集运算符（INTERSECT）的优先级高于并集运算符（UNION）和差集运算符（EXCEPT）​。但是 Oracle 和 SQLite 中所有集合运算符的优先级相同。
• 相同的集合运算符按照从左至右的顺序执行。
• 某些数据库支持使用括号调整多个集合运算符的执行顺序。

sql
-- mss pg sqlite
SELECT 1 AS n
UNION ALL
SELECT 1
INTERSECT
SELECT 1;
-- mss pg
/*
n
-
1
1
*/

-- sqlite
/*
n
-
1
*/

--oracle
SELECT 1 AS n FROM dual
UNION ALL
SELECT 1 FROM dual
INTERSECT
SELECT 1 FROM dual;
-- oracle
/*
n
-
1
*/

-- oracle 需要加上 dual 表
SELECT 1 AS N
UNION ALL
SELECT 1
UNION
SELECT 1;
/*
n
-
1
*/
SELECT 1 AS N
UNION
SELECT 1
UNION ALL
SELECT 1;
/*
n
-
1
1
*/

-- 使用括号修改顺序
-- mss pg，oracle 需要加上 dual 表
SELECT 1 AS N
UNION ALL
(SELECT 1
UNION
SELECT 1);
/*
n
-
1
1
*/

• MySQL 和 SQLite 目前不支持修改集合运算符优先级。

参考

• Set operations (SQL)