sql-unions-operator

SQL - 使用 UNION 组合结果

SQL UNION 运算符用于将两个或多个 SELECT 语句的结果集组合成一个单一的结果集。它是一个强大的工具，用于从具有相似结构的多个表中聚合数据。

UNION 的规则

要成功使用 UNION，涉及的 SELECT 语句必须“联合兼容”，这意味着它们必须遵循以下规则：

• 列数相同： 每个 SELECT 语句必须检索相同数量的列。
• 兼容的数据类型： 每个 SELECT 语句中对应列的数据类型必须兼容（例如，您可以将 INT 与 SMALLINT 联合，但不能与 DATE 联合）。
• 顺序相同： 在每个 SELECT 语句中，列的顺序必须相同。

最终结果集中的列名取自第一个 SELECT 语句。

UNION 与 UNION ALL

这是正确性和性能方面的一个关键区别。

• UNION：从最终结果集中移除重复行。为此，它必须执行额外的处理步骤（通常是排序或哈希）来识别并消除重复项。这会使其变慢。
• UNION ALL：包含所有 SELECT 语句中的所有行，包括任何重复项。它只是简单地追加结果，并且由于跳过了去重步骤，因此显著更快。

最佳实践： 除非您有明确的去重需求，否则请始终使用 UNION ALL。在大型结果集上，性能提升可能非常显著。

示例：UNION vs UNION ALL

考虑两个表：一个用于 ACTIVE_EMPLOYEES（在职员工），一个用于 FORMER_EMPLOYEES（前员工）。

CREATE TABLE ACTIVE_EMPLOYEES (ID INT, NAME VARCHAR(100));
CREATE TABLE FORMER_EMPLOYEES (ID INT, NAME VARCHAR(100));

INSERT INTO ACTIVE_EMPLOYEES VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO FORMER_EMPLOYEES VALUES (2, 'Bob'), (3, 'Charlie');

使用 UNION 获取所有员工的唯一列表：

SELECT ID, NAME FROM ACTIVE_EMPLOYEES
UNION
SELECT ID, NAME FROM FORMER_EMPLOYEES;
-- 结果：(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')

使用 UNION ALL 获取完整列表，包括重复项：

SELECT ID, NAME FROM ACTIVE_EMPLOYEES
UNION ALL
SELECT ID, NAME FROM FORMER_EMPLOYEES;
-- 结果：(1, 'Alice'), (2, 'Bob'), (2, 'Bob'), (3, 'Charlie')

与其他子句结合使用

像 WHERE 和 ORDER BY 这样的子句可以与 UNION 一起使用，但它们的位置很重要。

• WHERE：WHERE 子句可以应用于每个单独的 SELECT 语句，以便在联合发生之前过滤其结果。
• ORDER BY 和 LIMIT：这些子句应用于最终的、组合后的结果集。它们必须只出现一次，位于整个语句的最末尾。

示例：过滤和排序

SELECT NAME FROM ACTIVE_EMPLOYEES WHERE ID > 1
UNION ALL
SELECT NAME FROM FORMER_EMPLOYEES WHERE NAME LIKE 'C%'
ORDER BY NAME DESC;

此查询从第一个表获取“Bob”，从第二个表获取“Charlie”，将它们组合，然后对最终结果进行排序，得到：（“Charlie”，“Bob”）。

使用别名重塑数据

UNION 非常适合创建来自不同源数据的统一视图。您可以添加字面量值作为新列，例如“来源”或“类型”字段。

示例：添加状态列

SELECT NAME, 'Active' AS Status FROM ACTIVE_EMPLOYEES
UNION ALL
SELECT NAME, 'Former' AS Status FROM FORMER_EMPLOYEES;

这会生成一个单一、清晰的列表，其中包含每个人的状态。

NAME	Status
Alice	Active
Bob	Active
Bob	Former
Charlie	Former

其他集合运算符：INTERSECT 和 EXCEPT

SQL 提供了另外两个同样非常有用的集合运算符：

• INTERSECT：只返回在 两个 结果集中都出现的行。（例如，SELECT NAME FROM ACTIVE_EMPLOYEES INTERSECT SELECT NAME FROM FORMER_EMPLOYEES; 将返回“Bob”。）
• EXCEPT（或 Oracle 中的 MINUS）：返回第一个结果集中出现但在第二个结果集中 不 出现的行。（例如，SELECT NAME FROM ACTIVE_EMPLOYEES EXCEPT SELECT NAME FROM FORMER_EMPLOYEES; 将返回“Alice”。）