sql-except-clause

SQL 集合操作：EXCEPT、INTERSECT 和 UNION

SQL 集合操作符简介

SQL 提供了强大的集合操作符，可以将两个或多个 SELECT 语句的结果合并为一个结果集。这些操作符——EXCEPT、INTERSECT 和 UNION——的行为类似于数学集合论中的对应概念。

集合操作符规则：

1. SELECT 语句必须具有相同数量的列。
2. 对应列的数据类型必须兼容。
3. 默认情况下，所有这些操作符都返回唯一的行（去重）。若要包含重复行，请使用 ALL 关键字（例如 UNION ALL）。

我们用两个简单的表来举例：full_time_employees（全职员工）和 managers（经理）。

CREATE TABLE full_time_employees (employee_name VARCHAR(50));
CREATE TABLE managers (manager_name VARCHAR(50));

INSERT INTO full_time_employees VALUES ('Alice'), ('Bob'), ('Charlie'), ('David');
INSERT INTO managers VALUES ('Charlie'), ('David'), ('Eve');

SQL EXCEPT 操作符：查找差异

EXCEPT 操作符返回第一个 SELECT 语句中存在但第二个 SELECT 语句中不存在的所有唯一行。它用于查找两个集合之间的差异。

**RDBMS 支持情况：**
- **支持：** PostgreSQL、SQL Server、SQLite。
- **不支持：** MySQL（请参阅下方的替代方案）。
- **替代名称：** Oracle 使用 `MINUS` 操作符实现相同功能。

示例：查找不是经理的员工

SELECT employee_name FROM full_time_employees
EXCEPT
SELECT manager_name FROM managers;

此查询返回 ‘Alice’ 和 ‘Bob’，因为他们存在于 full_time_employees 表中，但不存在于 managers 表中。

员工姓名
Alice
Bob

SQL INTERSECT 操作符：查找交集

INTERSECT 操作符返回同时存在于两个 SELECT 语句结果中的所有唯一行。它用于查找集合的共同元素（交集）。

示例：查找同时也是经理的员工

SELECT employee_name FROM full_time_employees
INTERSECT
SELECT manager_name FROM managers;

此查询返回 ‘Charlie’ 和 ‘David’，因为他们的名字同时出现在两个表中。

员工姓名
Charlie
David

SQL UNION 操作符：合并结果

UNION 操作符将两个或多个 SELECT 语句的结果合并为一个结果集，并移除所有重复行。若要保留重复行，请使用 UNION ALL。

示例：获取所有人（员工和经理）的列表

SELECT employee_name FROM full_time_employees
UNION
SELECT manager_name FROM managers;

这将返回所有名称的唯一列表：‘Alice’、‘Bob’、‘Charlie’、‘David’ 和 ‘Eve’。

处理不支持 EXCEPT 的数据库（如 MySQL）

如果您的数据库（例如 MySQL）不支持 EXCEPT，您可以使用 LEFT JOIN 并检查 NULL 值来达到相同的效果。这是一种非常常见且重要的 SQL 模式。

LEFT JOIN ... WHERE ... IS NULL 模式

为了找到不是经理的全职员工，我们可以将员工表与经理表进行 LEFT JOIN。对于那些不是经理的员工，经理表中的相应列将为 NULL。

SELECT
    e.employee_name
FROM
    full_time_employees AS e
LEFT JOIN
    managers AS m ON e.employee_name = m.manager_name
WHERE
    m.manager_name IS NULL;

此查询产生的结果与 EXCEPT 示例完全相同：‘Alice’ 和 ‘Bob’。这种模式是任何 SQL 开发人员的基本工具，尤其是在使用 MySQL 时。