28. 子查询与自连接

本节目标：学完本章你能把一个查询”嵌套”进另一个查询里（子查询），也能让一张表”自己和自己连接”（自连接），并知道什么时候该用它们、什么时候该优先用 JOIN。

前面学了 JOIN 是把两张表横向拼起来。但有些问题，用”一条 SQL 里再套一条 SQL”来想会更自然——比如”找出金额大于平均订单金额的订单”。这里”平均订单金额”本身就得先查一次，查完才能当条件去筛。这种”查询里的查询”，就是 子查询（Subquery）。而”自连接（Self-Join）“是 JOIN 的一个特例：当要比较的数据都在同一张表里时，让这张表”自己连自己”。

本章我们仍然围绕统一示例表 users 和 orders 来演示。

子查询是什么

子查询，就是一个 SELECT 语句嵌套在另一条 SQL 语句内部。它最常被放在 WHERE、FROM、SELECT 子句里。SQLite 规定：子查询必须用一对括号 () 包起来。

sqlite> SELECT id, amount
   ...> FROM orders
   ...> WHERE amount > (
   ...>   SELECT AVG(amount) FROM orders
   ...> );

这句里，(SELECT AVG(amount) FROM orders) 就是子查询，它先算出所有订单的平均金额，外层查询再拿这个平均值去筛选”大于平均”的订单。子查询返回的是一个值（标量），这种叫”标量子查询”。

Note

子查询按”能不能独立运行”分成两类：不依赖外层的叫非关联子查询（上面这个就是，它可以单独跑）；依赖外层传值的叫关联子查询（后面会讲，它不能单独跑，要对着外层每一行重算一次）。

在 WHERE 里用子查询：配合 IN

当子查询返回”一列多个值”时，外层就不能用 = 比较了，要用 IN 来判断”外层的值是否在这个集合里”。

sqlite> SELECT name
   ...> FROM users
   ...> WHERE id IN (
   ...>   SELECT DISTINCT user_id FROM orders
   ...> );

这句的意思是：先从 orders 里取出”下过单的所有 user_id”（去重），再在 users 里找出 id 在这个集合里的用户——也就是”下过单的用户名单”。这比先查订单再回程序里循环查用户要干净得多。

Warning

常见坑：外键默认 OFF！上面子查询里 orders.user_id 指向 users.id，这只是我们约定的语义，SQLite 默认并不会阻止你写入一个 user_id 在 users 里根本不存在的”脏订单”，也不会因为删了用户就自动清理他的订单。若想让数据库真正强制这种参照关系（非法 user_id 写入时报错），需要显式 PRAGMA foreign_keys = ON; 且每次新连接都要重开，并在建表时写 FOREIGN KEY(user_id) REFERENCES users(id)。子查询/连接只是”按值关联取数”，不负责”保证数据合法”。

在 FROM 里用子查询：派生表

子查询还能放在 FROM 后面，当成一个”临时表”来用，这种临时表叫派生表（Derived Table），必须起个别名。

sqlite> SELECT AVG(per_user.total)
   ...> FROM (
   ...>   SELECT user_id, SUM(amount) AS total
   ...>   FROM orders
   ...>   GROUP BY user_id
   ...> ) AS per_user;

这里内层先算出”每个用户的订单总金额”，外层再对这些总金额求平均——即”用户平均消费额”。为什么不能一步写成 AVG(SUM(amount))？因为 SQL 不允许在同一个 SELECT 里先聚合再对聚合结果再聚合，必须借助 FROM 子查询（或后面要学的 CTE）把它拆成两步。

关联子查询：逐行依赖外层

关联子查询的特别之处在于：它引用了外层查询的列，所以对外层处理的每一行，它都要重新算一次。比如”找出金额高于该用户自身平均消费水平的那些订单”：

sqlite> SELECT o1.id, o1.user_id, o1.amount
   ...> FROM orders AS o1
   ...> WHERE o1.amount > (
   ...>   SELECT AVG(o2.amount)
   ...>   FROM orders AS o2
   ...>   WHERE o2.user_id = o1.user_id
   ...> );

内层用 o2.user_id = o1.user_id 引用了外层 o1 的当前行用户，于是它针对每个订单，都去算”这个订单所属用户的平均金额”再比较。关联子查询表达力强，但因为要对每行重算，数据量大时要留意性能——这时往往可以用 JOIN 或窗口函数改写得更高效。

自连接：一张表自己连自己

自连接不是新语法，而是 JOIN 的一种用法：当要比较的数据都在同一张表里（比如组织架构里”员工和上级都在 employees 表”），就让这张表同时扮演”左表”和”右表”两个角色。因为不能真的写两遍同一张表名，所以必须给其中一个起别名来区分。

为了讲清楚自连接，我们临时引入一张带”上下级”关系的表（它和 users 结构无关，仅演示）：

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     TEXT,
  manager_id INTEGER   -- 指向同一张表里另一行的 id
);

想查出”每个员工及其上级的名字”，就让 employees 自己连自己：

sqlite> SELECT e.name AS 员工, m.name AS 上级
   ...> FROM employees AS e
   ...> LEFT JOIN employees AS m ON m.id = e.manager_id;

这里 e 是”员工视角”，m 是”上级视角”，连接条件 m.id = e.manager_id 把两者对上。用 LEFT JOIN 是为了让”最高领导”（manager_id 为 NULL）也能出现，只是上级那一列是 NULL。

回到我们的 users / orders：自连接同样适用于”同一张表内部的成对比较”。例如假设 users 里有多人，想找”同龄的用户对”，可以这样：

sqlite> SELECT DISTINCT a.name, b.name, a.age
   ...> FROM users AS a
   ...> INNER JOIN users AS b
   ...>   ON a.age = b.age AND a.id < b.id;   -- a.id < b.id 避免 (甲,乙) 和 (乙,甲) 重复，也避免自己配自己

a.id < b.id 是个关键技巧：既能排除”自己和自己配对”，又能避免”甲配乙”与”乙配甲”被算成两对。

Tip

实用技巧：凡是”同一张表里的行与行之间比较”，第一反应就想到自连接 + 别名。最常见场景是组织架构（员工/上级）、分类树（父节点/子节点）、以及”找相同特征的对子”。连接条件里加一个 < 或 <> 来控制是否包含自身与重复对。

子查询 vs JOIN：怎么选

很多人会纠结”这事用子查询还是用 JOIN 写”。经验法则：

表达”是否存在 / 是否属于某个集合”（EXISTS / IN）→ 子查询自然；
表达”把两张表拼成宽表再展示多列” → JOIN 更直观，可读性通常更好；
同样的逻辑，JOIN 在 SQLite 里大多能被优化器处理得和子查询一样快，所以优先选读起来更顺的那个；
多层嵌套、特别深的子查询可读性会迅速下降，这时可以考虑下一章的 CTE（WITH）把它”拆成有名字的步骤”。

Warning

常见坑：子查询里如果用 = 比较，但子查询实际返回了多行，SQLite 会报错”子查询返回多行”。忘记用 IN 而误用 = 是最典型的错误。另一个坑：自连接若忘了写 a.id <> b.id 之类的去重条件，会出现”自己和自己配对”以及”双向重复对”，结果行数翻倍甚至更多，务必在连接条件里约束清楚。

类比小结

子查询 = “先算一个小问题，把答案塞进大问题当条件”，像做数学题时先算括号里的。
自连接 = “让同一张表左手拉右手”，专门解决”表内行与行比较”的问题，记得一定起别名、加去重条件。
二者都不是新语法，而是已有 SELECT / JOIN 的”组合用法”；读不顺时，下一章的 CTE 能帮你把嵌套摊平。

学会了子查询和自连接，你处理”一个查询依赖另一个查询结果”的需求就不慌了。下一章我们看 UNION，学习怎么把”多个查询的结果上下叠在一起”。