企业官网建设流程全解析

在上一篇中，我们系统学习了JOIN的各种用法，掌握了如何将多张表的数据横向连接在一起。但在很多场景下，我们需要将一个查询的结果作为另一个查询的输入，这种“查询中的查询”就是子查询。此外，当需要将两个结构相似的结果集纵向合并时，就要用到合并查询（UNION）。

本文将带你掌握：

• 标量子查询、列子查询、行子查询
• FROM子句中的派生表
• EXISTS/NOT EXISTS关联子查询
• UNION与UNION ALL的使用场景
• 实战：查找从未借书的读者，以及其他高级过滤

1. 什么是子查询？

子查询是嵌套在另一个 SQL 语句（SELECT、INSERT、UPDATE、DELETE）中的SELECT查询。外层查询被称为“外部查询”或“主查询”，内层称为“子查询”或“内层查询”。

子查询必须用括号包裹，通常可以出现在：

• WHERE子句中（最常见的用法）
• FROM子句中（充当临时表）
• SELECT列表中（作为计算列）

按返回结果分类：

• 标量子查询：返回单个值（一行一列）
• 列子查询：返回一列多行
• 行子查询：返回一行多列
• 表子查询：返回多行多列

2. WHERE 中的子查询

2.1 标量子查询

标量子查询返回单个值，可以与比较运算符（=、>、<等）配合使用。

需求：查询库存大于平均库存的图书

SELECTtitle,stockFROMbooksWHEREstock>(SELECTAVG(stock)FROMbooks);

执行过程：先计算子查询得到平均库存（假设为 5.8），再对外层每一行判断stock > 5.8。

需求：查询与“张三”借阅了同一本书的读者

SELECTDISTINCTr.nameFROMreaders rJOINborrow_records brONr.id=br.reader_idWHEREbr.book_id=(SELECTbook_idFROMborrow_recordsWHEREreader_id=1ORDERBYborrow_dateDESCLIMIT1);

这里子查询获取读者 ID=1（张三）最近借阅的一本书的 ID，然后外部查询找出所有借过这本书的读者。

2.2 列子查询（多行一列）

当子查询返回多行时，不能直接用=，而要用IN、ANY、ALL等多行操作符。

IN —— 是否在列表中

查找所有被借阅过的图书：

SELECTtitleFROMbooksWHEREidIN(SELECTDISTINCTbook_idFROMborrow_records);

NOT IN —— 不在列表中

查找从未被借过的图书：

SELECTtitleFROMbooksWHEREidNOTIN(SELECTDISTINCTbook_idFROMborrow_records);

注意：NOT IN有一个陷阱——如果子查询结果中包含NULL，整个NOT IN结果将为空（因为NULL代表未知，任何值与NULL比较都返回UNKNOWN）。为了避免此问题，可以在子查询中加WHERE book_id IS NOT NULL，或者使用NOT EXISTS（稍后介绍）。

ANY / ALL 与比较运算符组合

• > ANY (子查询)：大于子查询结果中的任意一个值，等价于大于最小值。
• > ALL (子查询)：大于子查询结果中的所有值，等价于大于最大值。

-- 查询库存大于“文学”分类中任意一本书的库存（即大于文学类最低库存）SELECTtitle,stockFROMbooksWHEREstock>ANY(SELECTb.stockFROMbooks bJOINbook_category bcONb.id=bc.book_idJOINcategories cONbc.category_id=c.idWHEREc.name='文学');-- 查询库存大于“文学”分类中所有书的库存（即大于文学类最高库存）SELECTtitle,stockFROMbooksWHEREstock>ALL(SELECTb.stockFROMbooks bJOINbook_category bcONb.id=bc.book_idJOINcategories cONbc.category_id=c.idWHEREc.name='文学');

2.3 行子查询

行子查询返回一行多列，与外部查询的多列组合进行比较。

-- 查询与“张三”的出生日期和状态完全相同的读者-- (假设 readers 表有 birthdate 和 status 列)SELECT*FROMreadersWHERE(birthdate,status)=(SELECTbirthdate,statusFROMreadersWHEREname='张三');

行子查询在实际中使用较少，但理解它有助于阅读源码或某些自动生成的 SQL。

3. FROM 中的子查询：派生表

子查询也可以放在FROM子句中，充当一张临时表（派生表）。它必须有一个别名。

需求：统计每位读者的借阅次数，再筛选出借阅次数超过 2 次的读者

如果不用派生表，你可能尝试在WHERE中使用聚合函数——但WHERE不能使用聚合，这就需要用HAVING。而使用派生表可以将统计结果当作一张表来查询：

SELECTreader_name,borrow_countFROM(SELECTr.nameASreader_name,COUNT(br.id)ASborrow_countFROMreaders rLEFTJOINborrow_records brONr.id=br.reader_idGROUPBYr.id,r.name)ASreader_statsWHEREborrow_count>2;

• 内层查询生成一张包含读者名和借阅次数的临时表reader_stats。
• 外层查询从这张临时表中筛选borrow_count > 2。

派生表在复杂报表中经常使用，尤其在需要多次引用同一个聚合结果时。

4. EXISTS 与 NOT EXISTS

EXISTS用于判断子查询是否返回至少一行。它通常是一个关联子查询——子查询中引用了外部查询的列。

语法：

WHEREEXISTS(子查询)WHERENOTEXISTS(子查询)

4.1 用 EXISTS 找出有借阅记录的读者

SELECTnameFROMreaders rWHEREEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_id=r.id);

• 外部查询遍历readers每一行。
• 对于每一行，执行子查询：看borrow_records中是否有该读者的借阅记录。
• 如果子查询返回至少一行，EXISTS为TRUE，保留该读者。

习惯上，EXISTS子查询的SELECT列表写1或*，因为我们只关心行是否存在，不关心具体值。

4.2 用 NOT EXISTS 找出从未借书的读者

这正是我们这篇的实战核心：

SELECTnameFROMreaders rWHERENOTEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_id=r.id);

EXISTS vs IN：

• EXISTS通常比IN更高效，尤其是子查询结果集很大的时候，因为EXISTS只要找到一行匹配就立即返回真，不需要生成完整结果集。
• NOT EXISTS不受NULL问题困扰，比NOT IN更安全。
• 在关联列有索引时，两者性能差异不大，但EXISTS语义更清晰。

练习：查找至少被借阅过一次的图书

SELECTtitleFROMbooks bWHEREEXISTS(SELECT1FROMborrow_records brWHEREbr.book_id=b.id);

5. 合并查询：UNION 与 UNION ALL

JOIN是横向拼接，将不同表的列组合在一起；UNION是纵向拼接，将多个SELECT的结果集按行合并。使用UNION的前提是：

• 每个SELECT的列数相同
• 对应列的数据类型兼容

5.1 UNION vs UNION ALL

• UNION：合并后自动去重（相当于合并 +DISTINCT）。
• UNION ALL：保留所有行，不去重，性能更高。

5.2 使用场景

场景1：合并两个相似的查询结果

假设我们要生成一份“联系人”列表，同时包含读者和作者（假设有一个独立的 authors 表）：

SELECTnameAScontact_name,'读者'AScontact_typeFROMreadersUNIONSELECTauthor,'作者'FROMbooksORDERBYcontact_name;

场景2：按条件拆分查询再合并

查询“库存为0的图书”和“库存超过10的图书”，作为两个极端情况展示：

SELECTtitle,stock,'缺货'ASstatusFROMbooksWHEREstock=0UNIONALLSELECTtitle,stock,'库存充足'ASstatusFROMbooksWHEREstock>10ORDERBYstock;

这里使用UNION ALL是因为两个集合显然不会重复。

5.3 UNION 的排序与限制

• ORDER BY只能放在最后一个 SELECT 之后，对整个合并结果排序。
• 如果要单独对某个 SELECT 排序，可以配合括号和LIMIT：

  (SELECT...ORDERBY...LIMIT10)UNIONALL(SELECT...ORDERBY...LIMIT10);

6. 子查询在 INSERT/UPDATE/DELETE 中的应用

子查询不仅用于SELECT，还能嵌入到 DML 语句中。

6.1 INSERT … SELECT

从其他表复制数据：

-- 将2025年的借阅记录归档到 borrow_archive 表INSERTINTOborrow_archive(reader_id,book_id,borrow_date,due_date,return_date)SELECTreader_id,book_id,borrow_date,due_date,return_dateFROMborrow_recordsWHEREYEAR(borrow_date)=2025;

6.2 UPDATE 结合子查询

将所有“文学”类图书的库存加 1：

UPDATEbooksSETstock=stock+1WHEREidIN(SELECTbook_idFROMbook_category bcJOINcategories cONbc.category_id=c.idWHEREc.name='文学');

6.3 DELETE 结合子查询

删除没有任何借阅记录的读者：

DELETEFROMreadersWHEREidNOTIN(SELECTDISTINCTreader_idFROMborrow_records);

或者用NOT EXISTS（更安全）：

DELETEFROMreaders rWHERENOTEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_id=r.id);

7. 实战：综合运用

让我们回到图书管理系统，完成几个有挑战性的查询。

7.1 查找从未借过书的读者（两种方法对比）

方法一：LEFT JOIN + IS NULL

SELECTr.nameFROMreaders rLEFTJOINborrow_records brONr.id=br.reader_idWHEREbr.idISNULL;

方法二：NOT EXISTS

SELECTr.nameFROMreaders rWHERENOTEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_id=r.id);

两种结果相同，NOT EXISTS通常更直观地表达了“不存在”的语义，且性能通常更好。

7.2 找出借阅最活跃的读者（借阅次数 >= 所有读者的平均借阅次数）

使用派生表 + 标量子查询：

WITHreader_statsAS(SELECTreader_id,COUNT(*)AScntFROMborrow_recordsGROUPBYreader_id)SELECTr.name,rs.cntFROMreader_stats rsJOINreaders rONrs.reader_id=r.idWHERErs.cnt>=(SELECTAVG(cnt)FROMreader_stats);

这里引入了CTE（公用表表达式），MySQL 8.0 支持，比嵌套派生表更清晰。CTE 作为WITH子句定义，可被后续查询多次引用。

7.3 合并查询：生成“图书活跃度报告”

同时展示被借次数最多的 3 本书和最少的 3 本书：

(SELECTtitle,COUNT(*)ASborrow_count,'热门'ASlabelFROMbooks bJOINborrow_records brONb.id=br.book_idGROUPBYb.id,b.titleORDERBYborrow_countDESCLIMIT3)UNIONALL(SELECTtitle,COUNT(*)ASborrow_count,'冷门'ASlabelFROMbooks bJOINborrow_records brONb.id=br.book_idGROUPBYb.id,b.titleORDERBYborrow_countASCLIMIT3)ORDERBYborrow_countDESC;

7.4 查找借阅了所有“技术”类图书的读者

这个需求比较高级，需要关联子查询配合双重否定逻辑：

“借阅了所有技术类图书的读者”等价于“不存在一本技术类图书没有被该读者借阅过”。

SELECTr.nameFROMreaders rWHERENOTEXISTS(SELECT1FROMbooks bJOINbook_category bcONb.id=bc.book_idJOINcategories cONbc.category_id=c.idWHEREc.name='技术'ANDNOTEXISTS(SELECT1FROMborrow_records brWHEREbr.book_id=b.idANDbr.reader_id=r.id));

这个查询比较绕，建议你放慢阅读：最内层NOT EXISTS表示“该读者没有借过这本书”，外层NOT EXISTS表示“不存在这样的技术书”——即“该读者借过所有技术书”。

8. 小结

本文我们深入了子查询和合并查询的高级用法：

• EXISTS/NOT EXISTS是表达“存在/不存在”语义的首选，性能通常优于IN/NOT IN，且没有 NULL 陷阱。
• 合并查询UNION/UNION ALL纵向拼接结果集，注意列数和类型匹配。
• 子查询可以与INSERT、UPDATE、DELETE结合，实现基于其他表数据的增删改。

现在你已经掌握了单个 SELECT 的几乎所有技能。下一个阶段我们将进入数据库的核心原理——索引、事务与 JDBC 编程，让性能与安全再上一个台阶。在此之前，别忘了完成第二阶段的最后一篇项目实战，它将综合运用我们学过的所有知识！

思考题：

1. NOT IN有什么潜在陷阱？用NOT EXISTS如何改写？
2. 派生表和 CTE (WITH) 有什么区别？分别在什么场景下使用？
3. UNION和JOIN的根本区别是什么？它们能互相替代吗？

参考资料

• MySQL 8.0 Reference Manual - Subqueries
• MySQL 8.0 Reference Manual - EXISTS and NOT EXISTS
• MySQL 8.0 Reference Manual - UNION