INTERSECT 和 EXCEPT 的语义？MySQL 8.0 之前不支持，应该如何用 JOIN/EXISTS 模拟？

考察要点

这道题面试官想看你是否懂 SQL 标准中的集合运算法则，以及在特定数据库（如 MySQL 8.0 之前版本）不支持这些标准语法时，你是否具备逻辑等价转换的能力。核心考察点包括：

1. 集合运算语义：对 INTERSECT (交集) 和 EXCEPT (差集) 的精确理解，特别是它们如何处理 NULL 值和重复行。
2. SQL 模拟能力：能否熟练运用 JOIN、[NOT] EXISTS、[NOT] IN 等基础构件，实现复杂的逻辑。
3. 细节与陷阱：是否清楚 NOT IN 与 NULL 结合时的致命陷阱，以及 JOIN 在处理 NULL 时的行为，并给出正确、健壮的解决方案。

核心概念

INTERSECT 和 EXCEPT 是 SQL 标准中用于处理两个查询结果集的集合运算符。

1. INTERSECT (交集)

INTERSECT 返回同时存在于两个查询结果集中的所有唯一行。

• 语义：SELECT ... FROM t1 INTERSECT SELECT ... FROM t2
• 关键特性：
  1. 去重：结果集会自动去重（等价于对最终结果执行 DISTINCT）。
  2. NULL 处理：在集合运算中，两个 NULL 值被视为相等。这与 WHERE a = b 中 NULL = NULL 返回 UNKNOWN (实际为 false) 的行为完全不同。

数据示例： | table_a | | table_b | |:-------:| |:-------:| | id | | id | | 1 | | 1 | | 2 | | 3 | | 3 | | 3 | | NULL | | NULL |

 1SELECT id FROM table_a
 2INTERSECT
 3SELECT id FROM table_b;

输出结果：

2. EXCEPT (差集)

EXCEPT 返回存在于第一个查询结果集中，但不存在于第二个查询结果集中的所有唯一行。 （注意：在 Oracle 中，对应的关键字是 MINUS）

• 语义：SELECT ... FROM t1 EXCEPT SELECT ... FROM t2
• 关键特性：
  1. 去重：结果集同样会自动去重。
  2. NULL 处理：同样地，两个 NULL 值被视为相等。如果 t1 和 t2 中都有 NULL，EXCEPT 运算会将其抵消。

数据示例 (同上):

 1SELECT id FROM table_a
 2EXCEPT
 3SELECT id FROM table_b;

输出结果：

版本差异：

• MySQL: 8.0.31+ 才完整支持 INTERSECT 和 EXCEPT。之前版本需要手动模拟。
• PostgreSQL, SQL Server, Oracle (MINUS): 长期支持这些标准集合运算符。

多种解法

假设我们有 table_a 和 table_b，列结构相同（例如 c1, c2），我们需要在不支持 INTERSECT/EXCEPT 的 MySQL 5.7 中模拟它们。

模拟 INTERSECT

解法 1：使用 INNER JOIN 和空值安全比较运算符 <=>

这是最直观且最健壮的模拟方式。

• 标注：✅ 所有 MySQL 版本
• SQL 代码：

 1-- 假设比较 c1, c2 两列
 2SELECT DISTINCT a.c1, a.c2
 3FROM table_a AS a
 4INNER JOIN table_b AS b
 5  ON a.c1 <=> b.c1 AND a.c2 <=> b.c2;

• 复杂度：时间复杂度取决于 Join 策略。若 b 表的连接列上有索引，则接近 $O(N \log M)$ 或 $O(N+M)$（Hash Join）。空间复杂度取决于结果集大小。
• 索引依赖：在 table_b(c1, c2) 上建立复合索引可以极大提升性能。

说明：必须使用 MySQL 特有的空值安全比较运算符 <=> 来正确处理 NULL 值。如果使用 =，NULL = NULL 会返回 false，导致 NULL 的交集丢失。如果确定列不会为 NULL，则可以使用 =。DISTINCT 是必需的，因为 INTERSECT 的语义包含去重。

解法 2：使用 EXISTS 子查询

EXISTS 在处理 NULL 时也需要 <=> 配合，逻辑清晰。

• 标注：✅ 所有 MySQL 版本
• SQL 代码：

 1SELECT DISTINCT a.c1, a.c2
 2FROM table_a AS a
 3WHERE EXISTS (
 4  SELECT 1
 5  FROM table_b AS b
 6  WHERE a.c1 <=> b.c1 AND a.c2 <=> b.c2
 7);

• 复杂度：EXISTS 通常被优化为 Semi-Join，性能很高。若 b 表相关列有索引，对 a 表的每一行，在 b 表的查找都是对数或常数时间。
• 索引依赖：table_b(c1, c2) 上的复合索引至关重要。

模拟 EXCEPT

解法 1：使用 LEFT JOIN ... IS NULL

这是模拟差集的经典模式，同样需要注意 NULL 值。

• 标注：✅ 所有 MySQL 版本
• SQL 代码：

 1SELECT DISTINCT a.c1, a.c2
 2FROM table_a AS a
 3LEFT JOIN table_b AS b
 4  ON a.c1 <=> b.c1 AND a.c2 <=> b.c2
 5WHERE b.c1 IS NULL; -- 或任何 b 表中在 ON 条件里用到的非空列

• 复杂度：与 INNER JOIN 类似。
• 索引依赖：table_b(c1, c2) 上的复合索引是性能的关键。

说明：LEFT JOIN 会返回 a 表的所有行，对于 a 表中能在 b 表找到匹配的行，b 的列会有值；找不到的，b 的列则为 NULL。我们正是利用 b.c1 IS NULL 来筛选出那些在 a 中存在但在 b 中不存在的行。

解法 2：使用 NOT EXISTS 子查询

这是最安全、最推荐的模拟 EXCEPT 的方式，因为它完美避开了 NOT IN 的陷阱。

• 标注：✅ 所有 MySQL 版本
• SQL 代码：

 1SELECT DISTINCT a.c1, a.c2
 2FROM table_a AS a
 3WHERE NOT EXISTS (
 4  SELECT 1
 5  FROM table_b AS b
 6  WHERE a.c1 <=> b.c1 AND a.c2 <=> b.c2
 7);

• 复杂度：NOT EXISTS 通常被优化为 Anti-Join，性能优秀。
• 索引依赖：table_b(c1, c2) 上的复合索引是必须的。

常见低分回答

1. 模拟 INTERSECT 时使用 = 代替 <=>：

   sql

   ⧉⛶

    1-- 错误：当 c1 或 c2 为 NULL 时，无法正确匹配
    2SELECT DISTINCT a.c1, a.c2 FROM table_a a JOIN table_b b ON a.c1 = b.c1;

   这是最常见的错误，忽略了 NULL 在 JOIN 条件中的特殊行为。

2. 模拟 EXCEPT 时使用 NOT IN 且未处理 NULL：

   sql

   ⧉⛶

    1-- 极度危险：如果 table_b 的 c1 列中存在任何 NULL 值，整个查询将返回空集！
    2SELECT c1, c2 FROM table_a WHERE (c1, c2) NOT IN (SELECT c1, c2 FROM table_b);

   这是面试中的一个巨大减分项。因为 val NOT IN (..., NULL, ...) 的逻辑结果是 UNKNOWN，在 WHERE 子句中被当作 FALSE 处理，导致所有行都被过滤掉。

3. 模拟时忘记 DISTINCT：

   sql

   ⧉⛶

    1-- 错误：结果可能包含重复行，不符合 INTERSECT/EXCEPT 的去重语义
    2SELECT a.c1, a.c2 FROM table_a a JOIN table_b b ON a.c1 <=> b.c1 AND a.c2 <=> b.c2;

   这表明候选人对 INTERSECT/EXCEPT 的定义理解不完全。

4. 混淆 EXCEPT 和 NOT EXISTS 的去重行为：EXCEPT 会对 table_a 的结果先去重，再执行差集运算。而 NOT EXISTS 是对 table_a 的每一行进行判断。因此，模拟 EXCEPT 时，SELECT DISTINCT a.* 中的 DISTINCT 是必不可少的。

工程实践与优化

• 优先使用原生语法：在 MySQL 8.0+ 或其他支持的数据库中，应始终优先使用 INTERSECT 和 EXCEPT。代码更简洁、意图更明确，且数据库优化器能选择最高效的内部算法（如 Hash Aggregate 或 Sort-Merge Anti Join）。

• EXISTS vs IN：在模拟场景中，EXISTS/NOT EXISTS 通常比 IN/NOT IN 更优。

  • 性能：EXISTS 遇到第一个匹配即返回，无需扫描完子查询的全部结果。优化器也更容易将其转换为高效的 Semi-Join / Anti-Join。
  • 正确性：NOT EXISTS 能完美处理 NULL，而 NOT IN 存在致命的 NULL 陷阱。

• 执行计划分析：

  • 使用 EXPLAIN 分析模拟查询。对于 LEFT JOIN ... IS NULL 的 EXCEPT 模拟，你应该在 Extra 列看到 Using where，并且 Join 类型是 left join。
  • 对于 NOT EXISTS，你可能会在 Extra 列看到 Using anti-join (如 Not-exists 或 Materialize-not-exists)，这表示优化器采用了高效的反连接策略。
  • 关键是确保对 table_b 的访问类型（type）是 ref 或 eq_ref，而不是 ALL（全表扫描），这依赖于 table_b 上的索引。

• 数据量级影响：当 table_a 和 table_b 都非常大时，JOIN 可能会产生巨大的中间结果集。此时 EXISTS 的优势（找到即停）更为明显。数据库原生 INTERSECT/EXCEPT 可能会使用基于哈希的算法，在内存充足的情况下，性能通常优于基于排序或嵌套循环的 JOIN 模拟。

常见追问与延伸题目

1. 追问：如果要模拟保留重复行的 INTERSECT ALL 和 EXCEPT ALL，该怎么做？

   • 回答要点：这需要精确匹配每一行的出现次数。最佳方法是使用窗口函数 ROW_NUMBER()。
     • 模拟 INTERSECT ALL：分别为 table_a 和 table_b 的结果集，使用 ROW_NUMBER() OVER(PARTITION BY c1, c2 ORDER BY c1) 生成一个在每个重复组内唯一的序号。然后 INNER JOIN 这两个带有序号的结果集，连接条件为所有列相等且行号相等。
     • 模拟 EXCEPT ALL：同样使用 ROW_NUMBER() 生成序号。然后 LEFT JOIN 两个结果集，筛选出 b 表匹配不上的行（b.row_num IS NULL）。

2. 追问：你提到的 <=> 是 MySQL 特有的，如果要在严格遵循 SQL 标准的数据库（但假设它也不支持 INTERSECT）里模拟，该如何处理 NULL？

   • 回答要点：需要用更通用的 OR 和 IS NULL 组合来重写 ON 条件。
     • a.c1 <=> b.c1 的标准等价写法是 (a.c1 = b.c1 OR (a.c1 IS NULL AND b.c1 IS NULL))。
     • 这种写法更冗长，但跨数据库兼容性好。现代数据库优化器通常能识别这种模式并进行优化，但其性能可能略逊于专用的空值安全比较运算符。

3. 追问：为什么 NOT IN 在子查询有 NULL 时会“失效”？请从三值逻辑（Three-Valued Logic）的角度解释。

   • 回答要点：SQL 的逻辑运算遵循三值逻辑：TRUE, FALSE, UNKNOWN。
     • value IN (set) 的意思是 value = v1 OR value = v2 OR ...。
     • value NOT IN (set) 的意思是 value <> v1 AND value <> v2 AND ...。
     • 当集合中有一个值为 NULL 时，比如 val NOT IN (1, 2, NULL)，它等价于 val <> 1 AND val <> 2 AND val <> NULL。
     • 任何值与 NULL 的比较（=、<>）结果都是 UNKNOWN。
     • 逻辑表达式 ... AND UNKNOWN 的最终结果是 UNKNOWN。
     • 在 WHERE 子句中，只有结果为 TRUE 的行才会被保留，FALSE 和 UNKNOWN 都会被丢弃。因此，整个 NOT IN 条件永远无法为 TRUE，导致查询返回空集。