结构化查询语言(Structured Query Language)
表、视图与 SELECT 结果集与集合理论的对比
在 SQL 中,表、视图以及 SELECT 命令的结果与集合论中的集合有一定的相似性。在这种比较中,集合的元素对应于表、视图以及 SELECT 结果集中的行。然而,SQL 与集合理论之间也存在一些关键差异:
-
重复数据:
集合理论中的集合不允许有重复项,而 SQL 允许重复数据(即多重集合)。甚至在一个表中,不同的行也可以完全相同,因为 SQL 中并不强制要求使用主键。 -
顺序:
集合理论中的集合和多重集合是无序的,但在 SQL 中,SELECT 命令的结果可以通过可选的ORDER BY
子句强制排序。 -
集合操作:
SQL 中的UNION
、INTERSECT
和EXCEPT
操作处理由不同 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 提供的 UNION
、INTERSECT
和 EXCEPT
操作扩展了对多重集合的操作能力。
UNION
:合并两个 SELECT 结果,默认去重。INTERSECT
:返回两个结果的交集。EXCEPT
:返回第一个结果中独有的值。
这些操作在多表查询或复杂数据分析时非常有用。
给 MySQL 用户的提示
MySQL(5.5)不支持 EXCEPT
操作。但由于它不是一个基本操作,可以使用替代方法实现相同的功能。
给 Oracle 用户的提示
Oracle 使用关键字 MINUS
代替 EXCEPT
。
清理示例数据库
DELETE FROM person WHERE id > 10;
COMMIT;
ORDER BY
我们可以将集合操作与 SELECT 命令的所有其他元素组合使用,尤其是 ORDER BY
和 GROUP 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;
执行过程:
- 首先评估两个 SELECT,得到两个中间结果;
- 然后应用
UNION
,合并中间结果为最终结果集; - 最后对结果集
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
,我们可以在最后一步对整个结果集进行分组。
总结
ORDER BY
应用于UNION
的最终结果,而非中间结果。- 如果需要对单个 SELECT 的结果排序或分组,可以通过虚拟列或重构查询实现。
- 对
UNION
的结果进行GROUP BY
时,最好将UNION
包含在子查询中,明确分组的目标范围。