表、视图与 SELECT 结果集与集合理论的对比

在 SQL 中,表、视图以及 SELECT 命令的结果与集合论中的集合有一定的相似性。在这种比较中,集合的元素对应于表、视图以及 SELECT 结果集中的行。然而,SQL 与集合理论之间也存在一些关键差异:

  1. 重复数据
    集合理论中的集合不允许有重复项,而 SQL 允许重复数据(即多重集合)。甚至在一个表中,不同的行也可以完全相同,因为 SQL 中并不强制要求使用主键。

  2. 顺序
    集合理论中的集合和多重集合是无序的,但在 SQL 中,SELECT 命令的结果可以通过可选的 ORDER BY 子句强制排序。

  3. 集合操作
    SQL 中的 UNIONINTERSECTEXCEPT 操作处理由不同 SELECT 命令生成的多重集合。SQL 要求这些多重集合必须是相同类型,即列数相同,数据类型需对应(但不一定完全匹配,DBMS 会尝试将其转换为通用类型)。


UNION(并集)

UNION 操作将多个 SELECT 命令的结果合并在一起。结果包含在第一个或第二个中出现的所有值。

-- 注意这是一个完整的 SQL 语句(只有一个分号)
SELECT firstname  -- 第一个 SELECT 命令
FROM   person
  UNION           -- 合并两个中间结果
SELECT lastname   -- 第二个 SELECT 命令
FROM   person;

默认行为:去重

如果执行上例,你会发现结果中只有 17 个值,而不是预期的 20 个值(因为每个 SELECT 都返回 10 行)。这是因为 UNION 默认去除重复值。


DISTINCT / ALL

UNION 的行为可以通过 DISTINCT(默认)或 ALL 关键字修改:

  • DISTINCT 去除重复值;
  • ALL 保留所有值,包括重复值。
SELECT ...
  UNION [DISTINCT | ALL]  -- 去重或保留重复值
SELECT ...
[ -- 可以合并超过 2 个 SELECT 结果
  UNION [DISTINCT | ALL]
SELECT ...
];

注意(Oracle 用户):在 Oracle 中,不允许显式使用 DISTINCT 关键字(虽然它是默认行为)。


INTERSECT(交集)

INTERSECT 操作返回同时存在于两个中间结果中的值。

-- 插入一行数据以演示 INTERSECT
INSERT INTO person VALUES (21, 'Paul', 'Victor', DATE'1966-04-02', 'Washington', '078-05-1121', 66);
COMMIT;

-- 查找既是 firstname 又是 lastname 的值
SELECT firstname  -- 第一个 SELECT 命令
FROM   person
  INTERSECT       -- 查找共同值
SELECT lastname   -- 第二个 SELECT 命令
FROM   person;

注意(MySQL 用户):MySQL 不支持 INTERSECT,但可以通过非基础操作的替代方案实现相同功能。


EXCEPT(差集)

EXCEPT 操作返回只存在于第一个中间结果,而不在第二个中间结果中的值。

-- 查找只作为 firstname 出现而未作为 lastname 出现的值
SELECT firstname  -- 第一个 SELECT 命令
FROM   person
  EXCEPT          -- 查找第一个结果中有但第二个结果中没有的值
SELECT lastname   -- 第二个 SELECT 命令
FROM   person;

注意(MySQL 用户):MySQL 不支持 EXCEPT,但可以通过其他方法实现替代操作。


总结

SQL 提供的 UNIONINTERSECTEXCEPT 操作扩展了对多重集合的操作能力。

  • UNION:合并两个 SELECT 结果,默认去重。
  • INTERSECT:返回两个结果的交集。
  • EXCEPT:返回第一个结果中独有的值。

这些操作在多表查询或复杂数据分析时非常有用。

给 MySQL 用户的提示

MySQL(5.5)不支持 EXCEPT 操作。但由于它不是一个基本操作,可以使用替代方法实现相同的功能。

给 Oracle 用户的提示

Oracle 使用关键字 MINUS 代替 EXCEPT


清理示例数据库

DELETE FROM person WHERE id > 10;
COMMIT;

ORDER BY

我们可以将集合操作与 SELECT 命令的所有其他元素组合使用,尤其是 ORDER BYGROUP BY。但这可能会引发一些不确定性,以下解释相关细节。

SELECT firstname  -- 第一个 SELECT 命令
FROM   person
  UNION           -- 合并两个中间结果为一个结果
SELECT lastname   -- 第二个 SELECT 命令
FROM   person
ORDER BY firstname;

ORDER BY 的作用范围

ORDER BY 属于哪一部分?第一个 SELECT,第二个 SELECT,还是 UNION 的结果?根据 SQL 规则,集合操作(如 UNION)会在 ORDER BY 子句之前被评估(括号可以改变评估顺序)。因此,ORDER BY 排序的是最终结果,而不是任何中间结果。


重构示例

为了更清楚地说明这一点,我们可以对查询进行重构:

-- 等价语义
SELECT * FROM
  (SELECT firstname  -- 第一个 SELECT 命令
   FROM   person
     UNION           -- 合并两个中间结果为一个中间结果 't'
   SELECT lastname   -- 第二个 SELECT 命令
   FROM   person
  ) t                -- 't' 是 UNION 生成的中间结果的名称
ORDER BY t.firstname;

执行过程:

  1. 首先评估两个 SELECT,得到两个中间结果;
  2. 然后应用 UNION,合并中间结果为最终结果集;
  3. 最后对结果集 t 进行排序。

按独立 SELECT 的顺序排序

如果希望第一个 SELECT 的结果单独排序,并与第二个 SELECT 的结果无关,可以通过添加虚拟列实现。

SELECT '1' AS dummy, firstname
FROM   person
  UNION
SELECT '2', lastname
FROM   person
ORDER BY dummy, firstname;

虚拟列 dummy 用于区分 SELECT 的来源,从而在排序时优先考虑来源顺序。


GROUP BY

GROUP BY 的作用范围比 ORDER BY 更复杂。它只适用于其直接所在的 SELECT 语句,而不是整个 UNION

错误示例

以下示例无法正确执行,因为 GROUP BY 仅作用于第二个 SELECT,而不是 UNION

SELECT firstname
FROM   person
  UNION
SELECT lastname
FROM   person
GROUP BY firstname; -- 错误,GROUP BY 只作用于第二个 SELECT

另一种行为(可能不是你的意图)

SELECT firstname
FROM   person
  UNION
-- 我们对第二个 SELECT 的列 'lastname' 分组,结果是 7 个值
SELECT lastname
FROM   person
GROUP BY lastname;

让逻辑更清晰

为了对完整结果分组,可以将查询重构为以下形式:

SELECT * FROM
  (SELECT firstname  -- 第一个 SELECT 的列名决定了 UNION 的列名
   FROM   person
     UNION
   SELECT lastname
   FROM   person
  ) t
GROUP BY t.firstname; -- 现在可以对整个结果分组

通过将 UNION 的结果命名为 t,我们可以在最后一步对整个结果集进行分组。


总结

  1. ORDER BY 应用于 UNION 的最终结果,而非中间结果。
  2. 如果需要对单个 SELECT 的结果排序或分组,可以通过虚拟列或重构查询实现。
  3. UNION 的结果进行 GROUP BY 时,最好将 UNION 包含在子查询中,明确分组的目标范围。
Last modified: Tuesday, 28 January 2025, 1:05 PM