一、为什么使用子查询

需求：查价格高于全店平均价的商品。 麻烦在于——不知道平均价，这句话就写不下去。 人的做法是分两步： 先 SELECT AVG(price) FROM tb_goods 算出 1372.48， 再把这个数硬编进 WHERE price > 1372.48。 能出结果，但这条 SQL 是死的。 哪天加了一件商品，平均价变成 1500， 这条语句还是按 1372.48 算——它不知道数据变了。 子查询就是把第一步整个塞进第二步：

SELECT gname, price FROM tb_goods
WHERE price > (SELECT AVG(price) FROM tb_goods)

两条结果一模一样，区别是第二条永远跟着数据走。这就是子查询的价值—— 把「先算一个数」这件事交给数据库，而不是交给人的记性。

二、三种返回形态，三种接法

子查询按返回的形状分三类。分类不是为了考试，是因为接错了就报错——这是最容易翻车的地方。

第一类最自由——它就是一个值，能放在任何能放值的地方，包括 SELECT 列表、WHERE、HAVING， 甚至 ORDER BY。 第二类要给「能接多个值」的运算符。 = 只接一个，IN 接一串——用错就是下面这个场面。 第三类要起别名，而且别名不能省。 FROM (SELECT ...) AS t 里那个 t 是必须的， SQL Server 会直接报「没有为列或表指定别名」。

三、单值、多行与等号的边界

演示里做了一件事：把本来返回多行的子查询，硬塞给等号。

-- 子查询里有两条不及格记录，外面却写了 =
SELECT sname FROM tb_student
WHERE id = (SELECT student_id FROM tb_score WHERE score < 60)

SQL Server 会报错，原话是「子查询返回的值不止一个」。这个错很好认—— 看到它就知道：要么改用 IN，要么给子查询加条件让它只返回一行。 但本沙箱（SQLite）一声不吭，直接给了第一行。 它把多行子查询当成了单值，取第一行就交差。 这两种态度都很危险。 SQL Server 的严格让你当场知道写错了； SQLite 的宽松让你以为写对了，搬到 SQL Server 上才发现跑不通。 所以「本机能跑」不等于「语句正确」。 换个数据库就报错的写法，是典型的可移植性坑。

四、嵌套子查询：从最里面往外读

两层很常见，三层就有点吓人了。 演示里那两条是这么写的：

-- 两层：哪些学生有高于全校平均分的成绩
SELECT DISTINCT sname FROM tb_student WHERE id IN (
    SELECT student_id FROM tb_score
    WHERE score > (SELECT AVG(score) FROM tb_score))

-- 三层：这些学生分布在哪些班
SELECT cname FROM tb_class WHERE id IN (
    SELECT class_id FROM tb_student WHERE id IN (
        SELECT student_id FROM tb_score
        WHERE score > (SELECT AVG(score) FROM tb_score)))

读这种语句的方法是从最里面往外读，一层一层把结果替换进去。千万别从上往下硬看—— 上面那层的意思，取决于下面那层给了什么。 三层那条读起来是这样的： 先算全校平均分（74.4），再挑出超过它的成绩记录，再换成学生 id， 再换成班级 id，最后换成班名。 层数没有硬上限——SQL Server 允许 32 层。 但人读得懂的极限是两三层，超过三层通常说明该拆成临时表或者 CTE 了。 硬叠下去， 改的人自己都看不懂。

五、相关子查询：标志是引用了外层列

前面几种叫「不相关子查询」——子查询自己能独立跑完，所以数据库只算一次。 相关子查询不一样，它里面用了外层的列：

SELECT st.sname, sc.subject, sc.score
FROM tb_score sc JOIN tb_student st ON st.id = sc.student_id
WHERE sc.score > (
    SELECT AVG(sc2.score) FROM tb_score sc2
    JOIN tb_student st2 ON st2.id = sc2.student_id
    WHERE st2.class_id = st.class_id)   -- 这一句是关键

st.class_id 属于外层，子查询自己算不出来。 所以外层每走到一行， 内层就得带着这一个值重跑一遍。 这就是「相关」的意思——两者绑在一起了。 业务意思很清楚： 每个学生里，成绩高于【他自己所在班】平均分的那几条。 换成不相关就写不出来，因为「班平均分」不是一个固定的数，它随学生变。

六、代价：索引才是决定因素

这一节是本篇最值钱的部分。 演示里做了两组对照，都是真跑取最快。

注意第二行：数据少了四分之三，反而从毫秒级掉到秒级。行数少了 75%， 耗时涨了 37 倍——这说明行数根本不是主要矛盾。 原因在索引。 有索引时，内层是「按 sid 找到那 10 行」； 没索引时，内层是「扫全表找那 10 行」。 5 千行乘 5 千行，就是两千五百万次比较。 所以结论不是「别用相关子查询」，而是：用之前先确认内层 WHERE 里那一列有索引。 没有索引的相关子查询，是新手把数据库拖垮最常见的一种方式。 还有一条顺带的方法论： 测这类差别，一定要在「有代表性的数据量」上测。 8 行数据的表上，两个数都是零点零几毫秒，谁快谁慢纯属噪声。

七、动手 Demo：子查询的三种形状与相关子查询的代价

这一段在六表样例库上走一遍：先看「硬编数」和「子查询」的区别，再看三种返回形态各自的接法， 然后两层、三层嵌套，最后相关子查询跑两组计时。 计时那两段要留意：有索引 65 毫秒，没索引 2455 毫秒——行数还少了一半多，慢了几百倍。

import sqlite3, time

c = sqlite3.connect(u":memory:")
# ... 建六表、装样例数据 ...

# 三种形态各来一条
print(c.execute(u"SELECT MAX(均分) FROM (SELECT ROUND(AVG(score),2) AS 均分 "
                u"FROM tb_score GROUP BY student_id)").fetchall())
print(c.execute(u"SELECT sname FROM tb_student WHERE id IN "
                u"(SELECT student_id FROM tb_score WHERE score < 60)").fetchall())


def clock(sql, repeat=3):
    c.execute(sql).fetchall()          # 先预热一次
    best = None
    for _ in range(repeat):
        t0 = time.perf_counter()
        c.execute(sql).fetchall()
        dt = (time.perf_counter() - t0) * 1000
        best = dt if best is None or dt < best else best
    return best


q_corr = (u"SELECT COUNT(*) FROM bs s WHERE s.v > "
          u"(SELECT AVG(x.v) FROM bs x WHERE x.sid = s.sid)")
q_join = (u"SELECT COUNT(*) FROM bs s JOIN "
          u"(SELECT sid, AVG(v) AS a FROM bs GROUP BY sid) t "
          u"ON t.sid = s.sid WHERE s.v > t.a")
print(u"有索引：%.2f / %.2f ms" % (clock(q_corr), clock(q_join)))

实跑输出：

--------------------------------------------------------------------
子查询基础
--------------------------------------------------------------------


  ① 为什么使用子查询：先把两步并成一步
  --------------------------------------------------------------------
  需求：查价格高于全店平均价的商品。不知道平均价，这条 SQL 就写不下去。
  人的做法是分两步跑：先算平均价，再拿这个数去比。

  第一步：SELECT AVG(price) FROM tb_goods   ->  1372.48

  第二步把算出来的数硬编进 SQL 能出结果，但平均价一变，这条 SQL 就错了。

  1> SELECT gname, price FROM tb_goods WHERE price > 1372.48
  gname       price
  -----------------
  联想笔记本  4999
  (1 行受影响)
  子查询就是把第一步整个塞进第二步里，让数据库自己去算这个数。

  1> SELECT gname, price FROM tb_goods WHERE price > (SELECT AVG(price) FROM tb_goods)
  gname       price
  -----------------
  联想笔记本  4999
  (1 行受影响)
  两条结果一模一样。区别是第二条永远跟着数据走，不需要人去改数。

  ② 子查询的使用：三种返回形态，三种接法
  --------------------------------------------------------------------
  子查询按返回的形状分三类，接口子句完全不同——这是最容易写错的地方。
| 返回形态 | 典型写法 | 能放在哪 |
|---|---|---|
| 单个值（1 行 1 列） | = (SELECT AVG(price)...) | 任何能放值的地方 |
| 一列多行 | IN (SELECT id FROM ...) | =、IN、ANY、ALL 之后 |
| 一张多行多列的表 | FROM (SELECT ...) AS t | FROM 后面当表用 |


  ① 单值子查询：各班均分里最高的那个是多少？

  1> SELECT c.cname AS 班级, ROUND(AVG(sc.score), 2) AS 均分
     FROM tb_score sc JOIN tb_student st ON st.id = sc.student_id JOIN tb_class c ON
         c.id = st.class_id
     GROUP BY c.cname
  班级      均分
  ---------------
  计科2301  80.5
  计科2302  73.25
  软工2301  79
  (3 行受影响)
  这是一张表（多行多列），把它当子查询塞进 FROM 里，外面再套 MAX：

  1> SELECT MAX(均分) AS 最高班级均分
     FROM (SELECT ROUND(AVG(sc.score), 2) AS 均分 FROM tb_score sc JOIN tb_student
         st ON st.id = sc.student_id GROUP BY st.class_id) t
  最高班级均分
  ------------
  80.5
  (1 行受影响)
  FROM 后面的子查询必须给别名（这里叫 t），否则 SQL Server 报语法错。
  这条同时用了两种形态：里面那层是「表」，外面套的是聚合函数。

  ② 一列多行：成绩不及格的那些学生是谁

  1> SELECT sname, class_id
     FROM tb_student
     WHERE id IN (SELECT student_id FROM tb_score WHERE score < 60)
  sname  class_id
  ---------------
  刘强   2
  (1 行受影响)
  ③ 一列多行硬塞给 = 会怎样：

  1> SELECT sname FROM tb_student WHERE id = (SELECT student_id FROM tb_score WHERE score < 60)
  sname
  -----
  刘强
  (1 行受影响)
  ! SQLite 一声不吭，直接给了第一行（刘强）——它把多行子查询当成了单值。
  SQL Server 不会这么宽容，同样一条会直接报错，原话是：
  消息 512，级别 16，状态 1，第 1 行
  子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，
  或子查询用作表达式时，这种情况是不允许的。
  两条路都对：要么用 IN／EXISTS 明确表示「可能多个」，要么加条件保证只返回一行。
  最怕的是在 SQLite 或 MySQL 上试通了，搬到 SQL Server 上才发现会报错。

  ③ 嵌套子查询：两层叠起来
  --------------------------------------------------------------------
  需求：哪些学生有高于全校平均分的成绩？

  1> SELECT DISTINCT sname
     FROM tb_student
     WHERE id IN (SELECT student_id FROM tb_score WHERE score > (SELECT AVG(score)
         FROM tb_score))
  sname
  -----
  张伟
  王芳
  陈静
  杨洋
  (4 行受影响)
  这是两层：最里面算出全校平均分，中间挑出超过它的成绩记录，最外面换成姓名。

  再加一层：这些学生分布在哪些班？

  1> SELECT cname
     FROM tb_class
     WHERE id IN (SELECT class_id FROM tb_student WHERE id IN (SELECT student_id
         FROM tb_score WHERE score > (SELECT AVG(score) FROM tb_score)))
  cname
  --------
  计科2301
  计科2302
  软工2301
  (3 行受影响)
  读嵌套子查询要从最里面往外读，一层一层把结果替换进去，别从上往下硬看。
  嵌套层数没有硬上限（SQL Server 允许 32 层），但人读得懂的极限是两三层。
  超过三层通常说明该拆成临时表或者 CTE，硬叠下去连写的人自己都改不动。

  ④ 相关子查询：子查询里用到外层的列
  --------------------------------------------------------------------
  前面几种叫「不相关子查询」，它们自己能独立跑完，只算一次。
  相关子查询的子查询体里引用了外层的列，外层每一行都要重新跑一遍内层。
  需求：每个学生里，成绩高于他自己所在班平均分的那几条。

  1> SELECT st.sname, sc.subject, sc.score
     FROM tb_score sc JOIN tb_student st ON st.id = sc.student_id
     WHERE sc.score > (SELECT AVG(sc2.score) FROM tb_score sc2 JOIN tb_student st2
         ON st2.id = sc2.student_id WHERE st2.class_id = st.class_id)
  sname  subject   score
  ----------------------
  张伟   高等数学  88
  王芳   高等数学  92
  王芳   大学英语  85
  陈静   大学英语  91
  (4 行受影响)
  子查询里出现了 st.class_id，它属于外层——这就是相关子查询的标志。

  ⑤ 相关子查询的代价：实测一下
  --------------------------------------------------------------------
  先造一张 2 万行的成绩表：2000 个学生，每人 10 门课，sid 上建了索引。
| 写法（2 万行，sid 有索引） | 最快一次（毫秒） | 结果行数 |
|---|---:|---:|
| 相关子查询（外层每行重算一遍内层） | 64.76 | 10000 |
| 先分组再连接（内层只算一次） | 11.75 | 10000 |

  结果一样（都是 10000 行），耗时差 5.5 倍。
  相关子查询要为外层 2 万行各跑一次内层聚合，等于 2 万次小查询。
  连接写法先把 2000 个均值一次算好存在临时表里，然后 join 一遍就完。

  但真正决定快慢的还不是写法，是索引。
  另建一张 5 千行的同类表，故意不建索引，同样两条语句再测一次：
| 写法（5 千行，sid 无索引） | 最快一次（毫秒） |
|---|---:|
| 相关子查询 | 2442.8 |
| 先分组再连接 | 6.0 |

  ! 数据少了四分之三，相关子查询反而从毫秒级掉到秒级：2442 毫秒。
  原因是内层那句 AVG 每次都得从头扫到尾，5 千行乘 5 千行就是两千五百万次比较。
  有索引时它是「按 sid 找那 10 行」，没索引时它是「扫全表找那 10 行」。
  结论：相关子查询不是不能用，但要保证内层 WHERE 里那一列上有索引。
  没有索引的相关子查询，是新手把数据库拖垮最常见的一种方式。

第一段那个 MAX(均分) 外面套的是聚合函数，里面是一张子查询表。这叫「派生表」—— 它不是值，是一张临时表，只在这次查询里活着。 计时函数里有一句「先预热一次」，那是必需的。 第一次执行要建执行计划、要把页读进缓存， 那个数不代表语句本身的耗时，代表的是「冷启动 + 语句」。 取最快的一次也值得说一句。 这台机器上后台进程时不时来打扰一下， 取平均会被偶发的抖动拉高——最快的那个数才最接近「语句本身要多久」。 最后那两行是本章的结论：65.16 对 12.64。 结果行数一模一样（都是 10000 行）， 差的是「内层跑了几次」——相关子查询跑了 2 万次，连接写法只跑了 1 次。

30 秒实验

1. 把 9.1a 那条「高于平均价」的子查询改成「高于最低价的两倍」，跑一次。 再手动改掉一件商品的价格，重跑——结果跟着变了，这就是子查询相对硬编数字的好处。
2. 把相关子查询那条里的 st.class_id 改成 st.id，看结果变成什么。 变成「高于他个人平均分」了——内层引用了哪个外层列，决定了它在算什么。
3. 把演示里那张 5 千行的表补上 sid 索引，再测一次。 2455 毫秒应该会掉到十几毫秒——这一个实验比看十遍结论都管用。

常见误区

1. 「子查询越短越好，最好一行写完」。短不等于清楚。 嵌套三层压成一行，改的人要拆半天——该换行就换行，该起别名就起别名。
2. 「多行子查询塞给等号，本机能跑就没问题」。SQLite 会默默取第一行， SQL Server 直接报「子查询返回的值不止一个」。换数据库就崩的写法，不算写对。
3. 「相关子查询一定比连接慢」。不一定。 有合适索引、又只需要第一行的时候，相关子查询反而更快—— 因为它能提前停。 慢的是「没索引还要全表扫」那种。

下一篇 9.1b 讲 IN、EXISTS、ANY、ALL 四个接子查询的运算符。 重点是 NOT IN 碰上 NULL 会静默返回空集——这个坑不实测一次很难相信。

• ☐ （本篇完，下一篇 9.1b IN、EXISTS、ANY 与 ALL 子查询）

相关笔记

• IN、EXISTS、ANY 与 ALL 子查询
• WHERE、GROUP BY、HAVING 与 ORDER BY
• COMPUTE、DISTINCT、TOP、WITH TIES 与 UNION
• 48 课 MySQL从入门到精通 · 索引
• 45 课 MySQL必知必会 · 索引
• 🪟 SQL Server从入门到精通 · 索引