第 21 篇集合操作符

imodouAbout 5 minMySQL

《MySQL 入门教程》第 21 篇集合操作符

数据表与集合理论中的集合非常类似，表是由行组成的集合。SQL 标准定义了基于行的各种集合操作：并集运算（UNION）、交集运算（INTERSECT）和差集运算（EXCEPT）。

UNION，用于将两个查询结果合并成一个结果集，返回第一个查询或者第二个查询中的数据；
INTERSECT，用于返回两个查询结果中的共同部分，即同时属于第一个查询结果和第二个查询结果的数据；
EXCEPT，用于返回出现在第一个查询结果中，但不在第二个查询结果中的数据。

这些操作符都可以将两个查询的结果集合并成一个结果集，但是合并的规则各不相同，如下图所示：

对于集合操作符，参与运算的两个查询结果需要满足以下条件：

结果集中字段的数量和顺序必须相同；
结果集中对应字段的类型必须匹配或兼容。

也就是说，两个查询结果的字段结构必须相同。如果一个查询返回 2 个字段，另一个查询返回 3 个字段，肯定无法合并。如果一个查询返回数字类型的字段，另一个查询返回字符类型的字段，通常也无法合并；不过 MySQL 可能会执行隐式的类型转换。

21.1 并集（UNION）

UNION 操作符用于将两个查询结果合并成一个结果集，返回第一个查询或者第二个查询中的数据：

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

其中，DISTINCT 表示将合并后的结果集进行去重；ALL 表示保留结果集中的重复记录；如果省略，默认为 DISTINCT。例如：

CREATE TABLE t1(id int);
INSERT INTO t1 VALUES (1), (2);

CREATE TABLE t2(id int);
INSERT INTO t2 VALUES (1), (3);

SELECT id AS n FROM t1 
UNION 
SELECT id AS m FROM t2;
n|
-|
1|
2|
3|

SELECT id AS n FROM t1 
UNION ALL
SELECT id AS m FROM t2;
n|
-|
1|
2|
1|
3|

第一个查询结果中只返回了一个数字 1；第二个查询结果中保留了重复的数字 1。另外，UNION 操作返回的字段名由第一个 SELECT 语句决定。

以下语句由于两个 SELECT 返回的字段数量不同而产生错误：

SELECT 1  AS n, 'a' AS s
UNION ALL
SELECT 1 AS m;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

以下语句通过隐式类型转换返回了合并之后的结果：

SELECT 1 AS n, 'a' AS s
UNION ALL
SELECT 1, 2;
n|s|
-|-|
1|a|
1|2|

返回结果中的第二个字段类型为字符串。

对于多个表的 UNION 操作，按照从前到后的自然顺序执行。例如：

SELECT 1 AS n
UNION ALL
SELECT 1
UNION 
SELECT 1;
n|
-|
1|

由于第二个 UNION 操作没有 ALL 选项，最终返回了去重之后的结果，也就是一个 1。

21.1.1 ORDER BY 和 LIMIT

如果要对整个 UNION 操作的结果进行排序和数量限定，可以将 ORDER BY 和 LIMIT 子句加到语句的最后。例如：

SELECT id AS n FROM t1
UNION ALL
SELECT id AS m FROM t2
ORDER BY n;
n|
-|
1|
1|
2|
3|

如果要对参与 UNION 的 SELECT 语句进行排序和数量限定，需要使用括号包含。例如：

(SELECT id AS n FROM t1 ORDER BY id DESC LIMIT 1)
UNION ALL
(SELECT id AS m FROM t2  ORDER BY id LIMIT 1);
n|
-|
2|
1|

这种排序操作不会影响到最终的结果顺序，如果要对最终结果进行排序，还需要在查询语句的最后再加上一个 ORDER BY 子句。

21.2 交集（INTERSECT）

MySQL 没有实现 SQL 标准中的 INTERSECT 操作符。按照定义，它可以返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据：

SELECT DISTINCT table1.column1, table1.column2, ...
  FROM table1 
  JOIN table2 
    ON (table1.column1 = table2.col1 AND table1.column2 = table2.col2 ...);

虽然 MySQL 不支持以上语法，但是我们可以通过 JOIN 查询实现相同的结果。

SELECT DISTINCT table1.column1, table1.column2, ...
  FROM table1 
  JOIN table2 
    ON (table1.column1 = table2.col1 AND table1.column2 = table2.col2 ...);

其中，DISTINCT 用于去除查询结果中的重复记录，实现和 INTERSECT 相同的效果。例如：

SELECT DISTINCT t1.id
FROM t1
JOIN t2 ON (t1.id = t2.id);
id|
--|
 1|

以上查询返回了 t1 和 t2 的交集。

还有一种方法也可以实现相同的结果，就是使用 IN 或者 EXISTS 子查询语句。例如：

SELECT DISTINCT id
FROM t1
WHERE id IN (SELECT id FROM t2);
id|
--|
 1|

21.3 差集（EXCEPT）

MySQL 没有实现 SQL 标准中的 EXCEPT 操作符。按照定义，它可以返回出现在第一个查询结果中，但不在第二个查询结果中的数据：

SELECT column1, column2, ...
  FROM table1 
EXCEPT
SELECT col1, col2, ...
  FROM table2;

虽然 MySQL 不支持以上语法，但是我们同样可以通过 JOIN 查询实现相同的结果。

SELECT column1, column2, ...
  FROM table1 
  LEFT JOIN table2
    ON (table1.column1 = table2.col1 AND table1.column2 = table2.col2 ...)
 WHERE table2.col1 IS NULL;

左外连接返回了 table1 中的所有数据，WHERE 条件排除了其中属于 table2 的数据，从而实现了 EXCEPT 操作符的效果。例如：

SELECT t1.id
FROM t1 
LEFT JOIN t2 ON (t2.id = t1.id)
WHERE t2.id IS NULL;
id|
--|
 2|

还有一种方法也可以实现相同的结果，就是使用 NOT IN 或者 NOT EXISTS 子查询语句。例如：

SELECT DISTINCT id
FROM t1
WHERE id NOT IN (SELECT id FROM t2);
id|
--|
 2|

第 21 篇 集合操作符

《MySQL 入门教程》第 21 篇 集合操作符