PostgreSQL - EXCEPT 运算符

PostgreSQL - EXCEPT 操作符

PostgreSQL 中的 EXCEPT 操作符用于比较两个 SELECT 语句。它返回第一个 SELECT 语句中存在但不在第二个 SELECT 语句结果集中的所有唯一行。用集合论术语来说，它执行集合差运算。

与 UNION 类似，两个 SELECT 语句中的列必须具有相同数量和兼容的数据类型。

语法

SELECT column1, column2, ...
FROM table1

EXCEPT

SELECT column1, column2, ...
FROM table2;

实践示例：查找未录用候选人

一个常见的用例是查找一个集合中存在但另一个集合中不存在的记录。假设我们有一个所有求职者的列表和另一个已录用求职者的列表。我们想找出仍在申请池中的候选人。

步骤 1：创建并填充表。

CREATE TABLE all_candidates (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL
);

CREATE TABLE hired_candidates (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL
);

-- 填充所有候选人
INSERT INTO all_candidates (full_name) VALUES
    ('Alice Johnson'),
    ('Bob Williams'),
    ('Charlie Brown'),
    ('Diana Prince');

-- 填充已录用候选人
INSERT INTO hired_candidates (full_name) VALUES
    ('Bob Williams'),
    ('Diana Prince');

步骤 2：使用 EXCEPT 操作符查找未被录用的候选人。

SELECT full_name
FROM all_candidates

EXCEPT

SELECT full_name
FROM hired_candidates;

此查询返回 all_candidates 中未出现在 hired_candidates 中的姓名：

全名
Alice Johnson
Charlie Brown

EXCEPT 与其他方法

您可以使用其他技术实现类似的结果，例如 LEFT JOIN 或 NOT IN。了解这些替代方案有助于您选择最合适的工具。

使用 LEFT JOIN

LEFT JOIN 结合 WHERE ... IS NULL 检查是一种非常常见的模式：

SELECT ac.full_name
FROM all_candidates AS ac
LEFT JOIN hired_candidates AS hc ON ac.full_name = hc.full_name
WHERE hc.full_name IS NULL;

此查询连接两个表，并筛选出 hired_candidates 表中没有匹配项的行，从而产生相同的结果。如果你需要在 SELECT 列表或 WHERE 子句中访问两个表中的列，LEFT JOIN 会更灵活。

使用 NOT EXISTS

NOT EXISTS 通常是性能最佳的选项，尤其是在大型数据集上，因为它一旦找到匹配项就可以停止搜索。

SELECT ac.full_name
FROM all_candidates AS ac
WHERE NOT EXISTS (
    SELECT 1
    FROM hired_candidates AS hc
    WHERE hc.full_name = ac.full_name
);

何时使用 EXCEPT

• 可读性： 对于简单的集合差，EXCEPT 通常是最具可读性和表达力的选项。其意图一目了然。
• 唯一性： EXCEPT 会自动从最终输出中移除重复行，就像 UNION 一样。
• 简洁性： 当你只需要第一个表中的列并且匹配逻辑是简单的行对行比较时，EXCEPT 优雅而简洁。