2.4 UNION、子查询、ANY 与 ALL

关系型数据库的一个核心思想是：所有的查询结果在逻辑上依然是一张二维表（即结果集，Relation）。既然是表，结果集本身就可以作为另一个更大查询的输入源。本节将详细探讨集合操作（UNION）、各种类型的子查询语法、以及极具迷惑性的特殊运算符（ANY 与 ALL）。

UNION 与 UNION ALL：合并结果集

当我们想把两个结构相同的不同查询合并，而不是做横向拼接（JOIN）时，会使用集合合并操作。

SELECT title FROM online_courses
UNION
SELECT title FROM classroom_courses;

1. UNION (去重合并)

UNION 的作用是将两张结果表的数据进行并集计算：

• 必须对齐结构：两边的 SELECT 语句所请求的列数必须完全相等，且对应位置上的列数据类型必须是相容/可隐式转换的。
• 物理开销高：为了去除可能存在的重复行，数据库在执行 UNION 时会先在内存（甚至溢出到磁盘临时文件）中进行排序 (Sort-Distinct)，这在大型数据集下可能成为性能瓶颈。

2. UNION ALL (直接合并)

SELECT title FROM online_courses
UNION ALL
SELECT title FROM classroom_courses;

• 不做整行去重：直接将两边的数据像卡牌堆叠一样缝合在一起，即使合并后相同的数据存在多份。
• 低成本执行：因为不需要检测并剔除多余的数据，数据库直接流式输出，无需排序，效率高得多。因此，在可以笃定数据不会重复、或者业务上乐于展现重复项时，优先使用 UNION ALL。

子查询的完整流派

把一个 SELECT 语句嵌入在另一个查询的某个子句里，内层的查询即被称为子查询 (Subquery/Nested Query)。

依据执行行为与返回形态，子查询可以做出极细致的分类：

A. 按照数据形态分类

1. 标量子查询 (Scalar Subquery)

内层子查询承诺仅且必定返回单一的一行一列（一个孤立的值，如平均数、最大值或具体某个学生的年龄）。

SELECT name, score
FROM students
WHERE score > (
  SELECT AVG(score)
  FROM students
);

*这里的 (SELECT AVG(score) FROM students) 就是一个标量子查询。它可以代替任何需要字面量的地方，如在 SELECT、WHERE、HAVING 等子句中。*

2. 列/多行子查询 (Column/List Subquery)

返回一列多行（即一个数组或值集合），可以用作 IN、NOT IN 等表达式的参数。

SELECT title FROM courses
WHERE id IN (
  SELECT DISTINCT course_id FROM enrollments
);

3. 行/表子查询 (Row/Table Subquery)

返回多行多列，一般作为 FROM 子句里的“虚拟视图”（即派生表 Derived Table），使用时通常必须用 AS 指定别名。

SELECT t.country, AVG(t.score)
FROM (
  SELECT * FROM students WHERE status = 'active'
) AS t
GROUP BY t.country;

B. 按照对齐关联度分类

1. 非相关子查询 (Self-contained / Independent Subquery)

内层子查询是一台“永动机”，它不需要知道外层任何表的状态就可以独自执行。

• 执行规律：优先执行一次内层子查询，将其结果常驻内存（或缓存），外层查询再逐行过滤。
• 上文演示的 AVG(score) 便是一个典型的非相关子查询。

2. 相关子查询 (Correlated Subquery)

内层子查询的 WHERE 条件里引用了外层查询的主表别名，导致内外完全绑定在一起。

-- 找出每个班级里，分数高于【该班自身平均分】的学生
SELECT s1.name, s1.score, s1.class_id
FROM students AS s1
WHERE s1.score > (
  SELECT AVG(s2.score)
  FROM students AS s2
  WHERE s2.class_id = s1.class_id -- 关联外层的 s1.class_id
);

• 执行规律：数据库不推荐直接“打平”它的求值。通常逻辑上，外层主查询每滑过一行，都必须把该行的 class_id 作为具体数值输送给内层，触发内层子查询进行新一轮的独立过滤，最后比对结果。这属于典型的 $O(N \times M)$ 循环复杂度。在大批数据下要格外防范其隐藏的性能消耗。

ANY 与 ALL 的深渊与“空值陷阱”

ANY 与 ALL 搭配比较符号，能做出十分数学化的集合比较：

• score > ANY (80, 90, 95)：由于 80 是里面的最小值，当前条件实际上相当于 score > 80。大于其中任意一个（最起码那一个）就算通过。
• score > ALL (80, 90, 95)：由于 95 是里面的最大值，当前条件实际上等价于 score > 95。小于/等于其中任何一丁点都不行，必须大于所有值。

> ANY、ALL 以及等价的 SOME 虽然极速好用，但在现代成熟项目中较少被编写，因为可以用更易懂的 JOIN + MIN/MAX 的方式进行改写，提升可读性。

🚨 警惕 NOT IN 的 NULL 指爆弹 (经典陷阱)

在集合计算中，如果将 NOT IN 或 != ALL 与一个含有 NULL 的子查询组合，会发生毁灭性的逻辑死锁。看看由于三值逻辑（True, False, Unknown）带来的奇异现象：

想象我们执行：

SELECT * FROM students
WHERE student_id NOT IN (1, 2, NULL);

很多人期望结果是不含 1 和 2 的其他人。然而，在 SQL 中，这个查询将返回绝对的空集 (无任何结果)！

• 逻辑解构：
• t NOT IN (1, 2, NULL) 在编译时等价于：

t != 1 AND t != 2 AND t != NULL

• 在 SQL 理论中，任何与 NULL 的不等式比较其结论都不为真不为假，而是 UNKNOWN。
• t != 1 AND t != 2 AND UNKNOWN 计算结果永远不会是真值。不论 t 是何值，整行条件永远被拦截。
• 避坑口诀：永远不要用 NOT IN 去连接一个内含 NULL 的子查询，或者在子查询中做前置过滤以丢弃 NULL。能用 NOT EXISTS 时就用 NOT EXISTS。

优化：什么时候替换或者抛弃子查询

子查询写起来非常自然直观，但它们在过去以“低性能”闻名——旧数据库优化器会在内层生成临时表，阻碍索引的使用。

现代成熟策略：

1. 替换为 JOIN：如果做子查询只是为了提取一些外表属性满足条件，通过 INNER JOIN 或 EXISTS 往往更利于生成高效的关联索引扫描。

2. 巧用全局公共表表达式 (CTE, Common Table Expression)： WITH 写法有助于将繁杂的多层嵌套理成顺溜的流式代码块，极其有利于可读性。有些时候还能促使底层走“物化保护(Materialization barrier)”，规避相关子查询被无限次内层重试。

-- 推荐：清晰漂亮的 CTE
WITH class_averages AS (
  SELECT class_id, AVG(score) AS avg_score
  FROM students
  GROUP BY class_id
)
SELECT s.name, s.score
FROM students AS s
JOIN class_averages AS ca
  ON s.class_id = ca.class_id
WHERE s.score > ca.avg_score;