五种主流数据库：集合运算

关系型数据库中的表与集合理论中的集合类似，表是由行（记录）组成的集合。因此，SQL 支持基于数据行的各种集合运算，包括并集运算（Union）、交集运算（Intersect）和差集运算（Except）。它们都可以将两个查询的结果集合并成一个结果集，但是合并的规则各不相同。

本文比较了五种主流数据库实现的集合运算，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

功能	MySQL	Oracle	SQL Server	PostgreSQL	SQLite
INTERSECT	✔️	✔️	✔️	✔️	✔️
INTERSECT ALL	✔️	✔️	❌	✔️	❌
UNION	✔️	✔️	✔️	✔️	✔️
UNION ALL	✔️	✔️	✔️	✔️	✔️
EXCEPT	✔️	✔️	✔️	✔️	✔️
EXCEPT ALL	✔️	✔️	❌	✔️	❌

执行 SQL 集合运算时，集合操作中的两个查询结果需要满足以下条件：

两个查询结果集中字段的数量必须相同。
两个查询结果集中对应字段的类型必须匹配或兼容。SQLite 使用动态数据类型，不要求字段类型匹配或兼容。

也就是说，参与运算的两个查询结果集的字段结构必须相同。如果一个查询返回 2 个字段，另一个查询返回 3 个字段，肯定无法进行合并。如果一个查询返回数字类型的字段，另一个查询返回字符类型的字段，通常也无法进行合并；不过，某些数据库（例如 MySQL）可能会尝试执行隐式的类型转换。

交集求同

SQL 交集运算的运算符是 INTERSECT，它可以用于获取两个查询结果集中的共同部分，也就是同时出现在第一个查询结果集和第二个查询结果集中的数据，如下图所示：

在这里插入图片描述
图中的 1 和 2 是两个查询结果集中都存在的元素，因此交集运算的结果只包含 1 和 2。

SQL 交集运算的语法如下：

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

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

注意：MySQL 8.0开始支持 INTERSECT 运算符以及 ALL 选项。PostgreSQL 支持完整的 DISTINCT 和 ALL 选项，Oracle 21c 开始支持 ALL 选项，SQL Server 以及 SQLite 支持简写的 INTERSECT。

我们首先创建两个简单的测试表 t_set1 和 t_set2。

sql">CREATE TABLE t_set1
(id INTEGER,name VARCHAR(10)
);
INSERT INTO t_set1 VALUES (1, 'apple');
INSERT INTO t_set1 VALUES (2, 'banana');
INSERT INTO t_set1 VALUES (3, 'orange');
CREATE TABLE t_set2
(id INTEGER,name VARCHAR(10)
);
INSERT INTO t_set2 VALUES (1, 'apple');
INSERT INTO t_set2 VALUES (2, 'banana');
INSERT INTO t_set2 VALUES (4, 'pear');

然后使用以下语句查找两个表中的共同记录。

sql">SELECT id, name
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;

查询返回的结果如下：

sql">id|name 
--|------1|apple 2|banana

其中，“apple”和“banana”是两个表中的共同数据。

以上示例中两个 SELECT 语句返回的列名都是 id 和 name，因此最终结果返回的列表也是 id 和 name。如果两个语句返回的列名不同，最终结果使用第一个语句返回的列名。

通常来说，交集运算都可以改写为等价的内连接查询。上面的查询语句可以改写为下面这样：

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);

注意，SELECT 列表中返回的全部字段（id 和 name）都必须作为连接查询的条件。

前文我们提到过，使用 SQL 集合运算的前提是，参与集合运算的两个查询结果集必须包含相同数量的字段，并且对应字段的数据类型必须匹配。因此，以下两个示例都会返回错误：

sql">SELECT id
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;SELECT id, id
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;

在第一个示例中，两个 SELECT 语句返回的字段数量不相同；在第二个示例中，两个 SELECT 语句返回的字段数据类型不一致。对于第二个查询示例，SQLite 不会返回错误。

并集存异

SQL 并集运算的运算符是 UNION，它可以用于计算两个查询结果集的相加，返回出现在第一个查询结果集或者第二个查询结果集中的数据，如下图所示。

在这里插入图片描述
图中的 1 和 2 是两个查询结果集中都存在的元素，不过它们在最终结果中只出现了一次，因为 UNION 运算符排除了查询结果中的重复记录。

SQL 并集运算的语法如下：

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

其中，DISTINCT 表示对合并的结果集进行去重操作，只保留不重复的记录。ALL 表示保留最终结果中的重复记录。如果省略，默认值为 DISTINCT。

以下是一个 UNION 运算符的示例：

sql">SELECT id, name
FROM t_set1 
UNION
SELECT id, name
FROM t_set2;

查询返回的结果如下：

sql">id|name 
--|------1|apple 2|banana3|orange4|pear

虽然“apple”和“banana”在两个表中都存在，但是它们在最终的结果中只出现了一次。

UNION 运算符可以改写为等价的全外连接查询。例如，上面的查询语句可以改写为下面这样：

sql">-- Oracle、Microsoft SQL Server、PostgreSQL 以及 SQLite 
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);

其中，全外连接可以返回左表和右表中的全部数据，COALESCE 函数的作用就是当左表字段为空时返回右表中的字段。MySQL 目前不支持全外连接查询。

如果我们想要保留并集运算结果中的重复记录，可以使用 UNION ALL 运算符。例如：

sql">SELECT id, name
FROM t_set1 
UNION ALL
SELECT id, name
FROM t_set2;

查询返回的结果如下：

sql">id|name 
--|------1|apple 2|banana3|orange1|apple 2|banana4|pear

此时，“apple”和“banana”在结果中分别出现了两次。

提示：通常来说，UNION ALL 运算符无须进行重复值的去除，其性能比 UNION 运算符更好（尤其在数据量比较大的情况下）。

对于 UNION 和 UNION ALL 运算符，两个查询结果必须包含相同数量的字段，同时对应字段的数据类型也要兼容。不过，MySQL 和 SQLite 会执行隐式的数据类型转换，例如：

sql">-- MySQL 和 SQLite
SELECT 1 AS id
UNION ALL
SELECT 'sql' AS name;

MySQL 将第一个查询返回的字段转换为字符串类型，SQLite 将第二个查询返回的字段转换为整数类型。查询返回的结果如下：

sql">id 
---
1 
sql

差集排他

SQL 差集运算的运算符是 EXCEPT，它可以用于计算两个查询结果集的相减，返回出现在第一个查询结果集中但不在第二个查询结果集中的数据，如下图所示。

在这里插入图片描述

图中第一个查询的结果只有元素 3 没有出现在第二个查询的结果中，因此差集运算的结果只返回了 3。SQL 差集运算的语法如下：

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

其中，DISTINCT 表示对合并的结果集进行去重操作，只保留不重复的记录。ALL 表示保留最终结果集中的重复记录。如果省略，默认值为 DISTINCT。

注意：MySQL 8.0 开始支持 EXCEPT 运算符以及 ALL 选项。Oracle 21c 开始支持 EXCEPT 关键字以及 ALL 选项，其以前的版本使用等价的 MINUS 运算符。PostgreSQL 支持完整的 DISTINCT 和 ALL 选项，SQL Server 以及 SQLite 支持简写的 EXCEPT。

以下是一个 EXCEPT 运算符的示例：

sql">SELECT id, name
FROM t_set1 
EXCEPT
SELECT id, name
FROM t_set2;

如果使用 Oracle 19c 以及更早的版本，等价的查询语句如下：

sql">-- Oracle
SELECT id, name
FROM t_set1 
MINUS
SELECT id, name
FROM t_set2;

查询返回的结果如下：

sql">id|name 
--|------3|orange

只有“orange”出现在表 t_set1 但不在表 t_set2 中。

差集运算可以改写为等价的左外连接或者右外连接，上面的查询语句可以改写为下面这样：

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;

其中的 WHERE 条件是关键，它保留了连接结果中 t_set2.id 为空的数据，也就是只在 t_set1 中出现的记录。

集合运算与排序

我们在使用集合运算符时需要注意几个事项，首先就是排序操作。如果我们想要对集合运算的结果进行排序操作，必须将 ORDER BY 子句写在整个查询语句的最后，集合运算符之前的 SELECT 语句中不能出现排序子句。

下面是一个错误的查询示例：

sql">-- 集合运算中的错误排序子句
SELECT id, name
FROM t_set1
ORDER BY id
UNION ALL
SELECT id, name
FROM t_set2;

无论我们使用哪种数据库，以上语句都会返回语法错误。因为在集合运算之前进行排序没有意义，最终结果的返回顺序可能会发生改变。正确的做法是在整个查询语句的最后指定排序操作，例如：

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

查询返回的结果如下：

sql">id|name 
--|------1|apple 1|apple 2|banana2|banana3|orange4|pear

运算符的优先级

另一个关于集合运算的注意事项就是 3 种集合运算符的优先级。当我们使用集合运算符将多个查询语句进行组合时，需要注意它们之间的优先级和执行顺序：

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

以下示例说明了不同集合运算符的执行优先级：

sql">-- Microsoft SQL Server、PostgreSQL 以及 SQLite
SELECT 1 AS n
UNION ALL
SELECT 1
INTERSECT 
SELECT 1;

以上语句在 Microsoft SQL Server 和 PostgreSQL 中返回的结果如下：

sql">n
-
1
1

查询返回了 2 个重复的 1。因为查询先执行 INTERSECT 运算符，结果包含 1 个 1。然后执行 UNION ALL 运算符，最终的结果保留了重复的 1。

以上语句在 Oracle 和 SQLite 中返回的结果如下：

sql">n
-
1

查询只返回了 1 个 1。因为查询先执行 UNION ALL 运算符，结果包含 2 个 1。然后再执行 INTERSECT 运算符，最终的结果去除了重复值。

以下示例说明了相同集合运算符的执行顺序：

sql">SELECT 1 AS n
UNION ALL
SELECT 1
UNION
SELECT 1;

查询返回的结果如下：

sql">n
-
1

以上语句只返回了 1 个 1，因为第二个 UNION 运算符去除了重复的记录。

如果我们将以上示例中的两个并集运算符交换位置：

sql">SELECT 1 AS n
UNION
SELECT 1
UNION ALL
SELECT 1;

查询返回的结果如下：

sql">n
-
1
1

以上语句返回了 2 个重复的 1，因为第二个 UNION ALL 运算符保留了重复的记录。

最后，我们可以在使用括号来修改多个集合运算符的执行顺序：

sql">-- MySQL、Oracle、Microsoft SQL Server 和 PostgreSQL
SELECT 1 AS n
UNION ALL
(SELECT 1
INTERSECT
SELECT 1);

以上示例先执行括号内的查询语句，因此查询返回的结果如下：

sql">n
-
1
1

SQLite 目前不支持这种修改集合运算符优先级的方式。