当前位置：首页 > 问答 > 正文

数据库except语句到底怎么用，搞懂它的区别和应用场景

关于数据库EXCEPT语句怎么用，以及它的区别和应用场景，我们可以把它想象成一个“找不同”的游戏，这个功能在SQL中非常实用，但并非所有数据库都支持（例如MySQL就不直接支持，需要用其他方法实现）。

EXCEPT语句到底是什么？怎么用？

EXCEPT运算符用于比较两个SELECT语句的结果集，并返回只在第一个结果集中出现，而不在第二个结果集中出现的所有行。

它的基本语法结构非常直观：

SELECT 列名1, 列名2, ...
FROM 表1
EXCEPT
SELECT 列名1, 列名2, ...
FROM 表2;

你可以这样理解：它从第一个查询（表1）的结果中，“减去”第二个查询（表2）的结果,然后把剩下的部分给你。

使用时的关键规则：

列的数量和顺序必须相同：两个SELECT语句查询的列数必须一样多，并且对应位置列的数据类型必须是兼容的（比如不能拿数字类型的列和文本类型的列直接比较）。
自动去重：EXCEPT运算的最终结果会自动去除重复的行，就像使用了DISTINCT一样，如果你需要保留所有重复项，在某些数据库（如SQL Server）中可以使用EXCEPT ALL。

举个简单的例子（来源：常见的教学示例）： 假设我们有两张表：

数据库except语句到底怎么用，搞懂它的区别和应用场景

现在我们想找出还没有被分配任何项目的员工,这时EXCEPT就派上用场了：

SELECT 员工ID, 姓名
FROM 所有员工表
EXCEPT
SELECT 员工ID, 姓名
FROM 已分配项目员工表;

这个查询的意思就是：从“所有员工”这个集合里，去掉“已分配项目的员工”这个集合，剩下的自然就是“未分配项目的员工”。

EXCEPT和其他类似操作的区别（特别是LEFT JOIN和NOT EXISTS）

EXCEPT的功能听起来似乎可以用其他查询方式实现，最常见的就是LEFT JOIN和NOT EXISTS,理解它们之间的区别非常重要。

EXCEPT vs. LEFT JOIN ... WHERE ... IS NULL

用LEFT JOIN实现上述查找未分配项目员工的查询,会写成这样：

数据库except语句到底怎么用，搞懂它的区别和应用场景

SELECT A.员工ID, A.姓名
FROM 所有员工表 A
LEFT JOIN 已分配项目员工表 B ON A.员工ID = B.员工ID
WHERE B.员工ID IS NULL;

区别点：
- 处理NULL值：这是关键区别，员工ID”列中存在NULL值，EXCEPT在比较时会认为两个NULL值是相等的（根据SQL标准），从而将其“减掉”，而LEFT JOIN的ON条件中，NULL = NULL的结果是未知（UNKNOWN），不会被匹配，但WHERE条件B.员工ID IS NULL会筛选出那些在B表中找不到匹配的行（包括因为NULL无法匹配的情况），在涉及NULL值时,两者的结果可能不同。
- 可读性：对于纯粹的集合“差集”操作，EXCEPT的语义更清晰、更直观，一眼就能看出是要找“A有B没有”的数据，LEFT JOIN的方式则需要绕个弯子理解。

EXCEPT vs. NOT EXISTS

用NOT EXISTS实现同样的需求：

SELECT 员工ID, 姓名
FROM 所有员工表 A
WHERE NOT EXISTS (
    SELECT 1
    FROM 已分配项目员工表 B
    WHERE B.员工ID = A.员工ID
);

区别点：
- 关联方式：NOT EXISTS是一个相关子查询，意味着外层查询的每一行都要执行一次内层查询来判断条件,而EXCEPT是两个独立的查询结果集进行集合运算。
- 性能：在没有NULL值干扰的理想情况下，现代数据库优化器对NOT EXISTS和EXCEPT的处理可能都非常高效，最终会生成相似的执行计划，但在某些复杂场景下，性能可能会有差异，这需要具体分析，NOT EXISTS通常在有索引的情况下表现优异。
- 灵活性：NOT EXISTS的关联条件可以更灵活，比如可以使用多个列进行复杂关联（ON A.id=B.id AND A.name=B.name）,而EXCEPT比较的是两个结果集所有对应列的完整行是否一致。

EXCEPT的典型应用场景

根据其“找不同”的核心特性,EXCEPT非常适合以下场景：

数据对比与审计：
- 场景：比较两个结构相同的表（比如今天的产品表快照和昨天的快照），找出哪些是新增加的产品记录,或者哪些产品记录被删除了。
- 示例：(SELECT * FROM 产品表_ EXCEPT (SELECT * FROM 产品表_昨天) 可以找出今天新增或修改过的产品（如果某行数据任何字段有变动，整行都会被看作不同而返回），但注意,这需要全字段比较。
查找“未完成”或“未发生”的项目：

就像开头的员工例子，广泛应用于查找“未下单的客户”、“未选修某门课程的学生”、“未支付的订单”等。
权限或资格校验：
- 场景：找出“拥有A权限但尚未完成安全培训的员工”，先查询有A权限的员工,再EXCEPT掉已完成安全培训的员工。
数据清洗与异常检测：
- 场景：假设有一个标准的“国家代码”表，你可以用实际业务数据中的国家代码字段去EXCEPT这个标准表，快速找出所有非法的、拼写错误的或非标准的国家代码。