波仔
🐬 第14章 使用子查询
📄 一句话概括

子查询就是「一条 SQL 里再套一条 SELECT」：内层先算，结果当外层的条件或列。 它把「查两次、人工搬结果」压成一次查询。 Demo 把「谁订了 TNT2」从两步并成一步，再套到三层； 当计算字段用时，五个客户分别数出 2／0／1／1／1 单、加起来正好 5； 故意不写表名前缀，每行都变成 5——不报错，只给错数。

📝 本章和前面几章的关系

上一章的 HAVING 里能塞聚集函数，这一章更进一步：能塞整条 SELECT。 子查询是「一次性的中间表」：不落库、不留名，用完就没了。 第 15 章的联结能替掉一部分子查询，而且通常更快——两条路都要会。

一、子查询：一条 SQL 里的 SELECT

子查询永远在括号里，位置有三种：WHERE 的条件里、SELECT 的列位置、FROM 后面。 本章讲前两种，FROM 后面那种（派生表）属于进阶。 执行顺序由内向外：内层先算出一个结果集，外层再拿它当条件用。所以读的时候也应该从里往外读。 硬规矩：用在 IN 后面的子查询只能返回一列；用在 = 后面必须保证只返回一行，多行就报错。

二、利用子查询进行过滤

教材那个例子分两步：先在 orderitems 里找出订了 TNT2 的单号（20005、20007）， 再拿这两个单号去 orders 里找客户号（10001、10004）。 两步并一步：把第一步整句塞进 IN (...)，一次拿到同样的两个客户号。 好处不只是少打一遍字——中间结果不用人工搬，也就不会抄错。 三层嵌套也是同一个套路：最内层找单号、中间层找客户号、最外层取客户名。 教材的建议是从里往外写、一层一层试，一次性写三层，错了不知道错在哪层。

三、作为计算字段使用子查询

子查询也能放在 SELECT 的列位置，这时它必须只返回一个值（一行一列）。 「每个客户下了几单」就是这么算的：外层遍历客户，内层数这个客户的单。 这种「内层引用了外层的列」的子查询叫相关子查询，它对外层每一行各跑一遍——五个客户就跑五遍。 验算的办法很简单：把这一列加起来。 2＋0＋1＋1＋1＝5，正好等于 orders 表的行数， 说明没漏也没重。

四、相关子查询的坑：全限定名

内层要引用外层的列，必须写全限定名：orders.cust_id = customers.cust_id。 只写列名的话，两边的 cust_id 都会被理解成内层自己的，条件恒成立。 结果就是每行都返回同一个数（orders 的总行数 5），而且不报错、不警告——这是最阴的一类 bug。 养成习惯：只要写了子查询，凡是同名列一律加表名前缀。 多打几个字，比事后排查半天便宜。

五、小结

教材小结：子查询是嵌套的 SELECT，由内向外执行，可作过滤条件也可作计算字段。 本课补三条：一是 IN 的子查询只能一列、= 的只能一行； 二是相关子查询要写全限定名，否则静默给错数；三是相关子查询逐行执行，数据大了要考虑换联结。

六、动手 Demo：从两步并成一步，再并成三层

Demo 六段：先老老实实分两步查，再把第一步塞进第二步，然后套到三层； 接着把子查询放进的列位置，最后故意漏掉表名前缀看它怎么骗人。 每段都盯着行数看：两步法与一条 SQL 的行数必须一样，那才叫等价。

import sqlite3
con = sqlite3.connect(":memory:")

# 第一步：谁订了 TNT2；第二步：这些单是谁下的
con.execute("SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'")
con.execute("SELECT cust_id FROM orders WHERE order_num IN (20005, 20007)")
# 合成一条：括号里先算，结果喂给外层
con.execute("SELECT cust_id FROM orders WHERE order_num IN "
            "(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')")
# 三层嵌套：由内向外读
con.execute("SELECT cust_name, cust_contact FROM customers "
            "WHERE cust_id IN (SELECT cust_id FROM orders "
            "WHERE order_num IN (SELECT order_num FROM orderitems "
            "WHERE prod_id = 'TNT2'))")
# 子查询当计算字段：每个客户下过几单（相关子查询）
con.execute("SELECT cust_name, "
            "(SELECT COUNT(*) FROM orders "
            "WHERE orders.cust_id = customers.cust_id) AS orders "
            "FROM customers")
# 不写全限定名：两边都是 orders 自己的列，恒成立，每行都给 5
con.execute("SELECT cust_name, "
            "(SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders "
            "FROM customers")

实跑输出：

--------------------------------------------------------------------
第十四课：使用子查询
--------------------------------------------------------------------

  【一】一个问题拆成两步：谁订了 TNT2
  SQL> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
  order_num
  ----------
  20005
  20007
  （2 行）
  SQL> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007)
  cust_id
  --------
  10001
  10004
  （2 行）
  **第一步查单号，第二步拿单号查客户。** 两步各跑一次，中间结果还得人工搬。

  【二】把第一步塞进第二步：子查询
  SQL> SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')
  cust_id
  --------
  10001
  10004
  （2 行）
  **同一件事，一条 SQL。** 括号里那句叫子查询，**它先算，结果喂给外层。**
  **规矩：子查询永远在括号里，而且必须只返回一列**（这里是 order_num 一列）。

  【三】三层嵌套：谁订了 TNT2，名字和电话是什么
  SQL> SELECT cust_name,
       cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'))
  cust_name          cust_contact
  --------------------------------
  Coyote Inc.        Y Lee
  Yosemite Place     Y Sam
  （2 行）
  **三层，由内向外读**：先找订了 TNT2 的单号，再找这些单的客户号，最后取客户名。
  **教材的建议是「从里往外写、一层一层试」**：先单独跑最内层，确认它给的东西对，再往外包一层。
  **一次性写完三层，错了不知道错在哪层。**

  【四】子查询当计算字段：每个客户下了几单
  SQL> SELECT cust_name, cust_state,
       (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name
  cust_name          cust_state     orders
  ---------------------------------------------
  Coyote Inc.        MI             2
  E Fudd             IL             1
  Mouse House        OH             0
  Wascals            IN             1
  Yosemite Place     AZ             1
  （5 行）
  **括号里那句跑了五遍**（每个客户一遍），
  所以这叫相关子查询——**它引用了外层的 customers.cust_id。**
  **五个数加起来正好 5**，和 orders 表的行数对上，这就是验算。

  【五】不写全限定名的后果
  SQL> SELECT cust_name,
       (SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders FROM customers ORDER BY cust_name
  cust_name          orders
  -----------------------------
  Coyote Inc.        5
  E Fudd             5
  Mouse House        5
  Wascals            5
  Yosemite Place     5
  （5 行）
  **每行都给 5。** 因为内层的 `cust_id = cust_id` 两边都是 orders 自己的列，
  **恒成立**——它算的是「orders 表一共几行」，和客户没关系。
  **规矩：相关子查询里的列名一律写全限定名**（`orders.cust_id = customers.cust_id`）。
  **不写不报错，只给错数**，这是最阴的一类 bug。

  【六】子查询不是万能的
  **子查询只能返回一列**，多列就报错；**IN 后面能接子查询，= 后面必须保证只一行。**
  **性能上，相关子查询是「外层每行跑一遍内层」**，数据一大就慢。
  **第 15 章的联结通常更快**，第 16 章还会讲怎么用联结替掉一部分子查询。

第一段分两步。 先得 20005、20007 两个单号，再得 10001、10004 两个客户。 第二段合成一条。 结果一模一样，少了一次人工搬运。 第三段三层。 从里往外：单号、客户号、客户名，最后两行：Coyote Inc. 与 Yosemite Place。 第四段当计算字段。 五行的单数 2／0／1／1／1，加起来 5，和 orders 的行数对上。 第五段漏掉表名前缀。 每行都成了 5——条件恒成立，它数的其实是 orders 表总共几行。 第六段没有输出，只有提醒：子查询只能一列，= 后面必须一行。

30 秒实验

  1. 把 prod_id = 'TNT2' 换成 'ANV01'，两段查询分别给几个客户？
  2. 用子查询算「每个客户下过几单」，再手工数一遍 orders 表核对。
  3. 把第四段的 orders.cust_id = customers.cust_id 改成只写 cust_id，看结果怎么变。

常见误区

  1. 以为子查询可以返回多列。IN 后面只能一列，多列直接报错。
  2. 相关子查询不写表名前缀。条件恒成立，每行给同一个数，还不报错。
  3. 把子查询当性能优化。相关子查询是逐行执行的，数据一大反而更慢。
自测三问（点开对答案） 1. 子查询写在哪里？执行顺序是怎样的？ —— 写在括号里，可出现在 WHERE 条件、SELECT 列位置、FROM 后面；执行时由内向外，内层先算。 2. 「谁订了 TNT2」用子查询怎么写成一条 SQL？ —— 最内层 `SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'` 拿单号， 中间层 `SELECT cust_id FROM orders WHERE order_num IN (...)` 拿客户号， 最外层再用 `IN` 到 customers 取名字。 3. 相关子查询里为什么必须写全限定名？ —— 因为内层也有同名列，不加前缀时两边都会被当成内层自己的列，条件恒成立，每行返回同一个数。

子查询能解决「先算一层再用」， 但一次只能查一张表的结果。 下一章把多张表摆在一起查——联结，SQL 真正的分水岭。

相关笔记