在上一篇中我们系统学习了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.idbr.reader_idWHEREbr.book_id(SELECTbook_idFROMborrow_recordsWHEREreader_id1ORDERBYborrow_dateDESCLIMIT1);这里子查询获取读者 ID1张三最近借阅的一本书的 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,stockFROMbooksWHEREstockANY(SELECTb.stockFROMbooks bJOINbook_category bcONb.idbc.book_idJOINcategories cONbc.category_idc.idWHEREc.name文学);-- 查询库存大于“文学”分类中所有书的库存即大于文学类最高库存SELECTtitle,stockFROMbooksWHEREstockALL(SELECTb.stockFROMbooks bJOINbook_category bcONb.idbc.book_idJOINcategories cONbc.category_idc.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.idbr.reader_idGROUPBYr.id,r.name)ASreader_statsWHEREborrow_count2;内层查询生成一张包含读者名和借阅次数的临时表reader_stats。外层查询从这张临时表中筛选borrow_count 2。派生表在复杂报表中经常使用尤其在需要多次引用同一个聚合结果时。4. EXISTS 与 NOT EXISTSEXISTS用于判断子查询是否返回至少一行。它通常是一个关联子查询——子查询中引用了外部查询的列。语法WHEREEXISTS(子查询)WHERENOTEXISTS(子查询)4.1 用 EXISTS 找出有借阅记录的读者SELECTnameFROMreaders rWHEREEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_idr.id);外部查询遍历readers每一行。对于每一行执行子查询看borrow_records中是否有该读者的借阅记录。如果子查询返回至少一行EXISTS为TRUE保留该读者。习惯上EXISTS子查询的SELECT列表写1或*因为我们只关心行是否存在不关心具体值。4.2 用 NOT EXISTS 找出从未借书的读者这正是我们这篇的实战核心SELECTnameFROMreaders rWHERENOTEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_idr.id);EXISTS vs INEXISTS通常比IN更高效尤其是子查询结果集很大的时候因为EXISTS只要找到一行匹配就立即返回真不需要生成完整结果集。NOT EXISTS不受NULL问题困扰比NOT IN更安全。在关联列有索引时两者性能差异不大但EXISTS语义更清晰。练习查找至少被借阅过一次的图书SELECTtitleFROMbooks bWHEREEXISTS(SELECT1FROMborrow_records brWHEREbr.book_idb.id);5. 合并查询UNION 与 UNION ALLJOIN是横向拼接将不同表的列组合在一起UNION是纵向拼接将多个SELECT的结果集按行合并。使用UNION的前提是每个SELECT的列数相同对应列的数据类型兼容5.1 UNION vs UNION ALLUNION合并后自动去重相当于合并 DISTINCT。UNION ALL保留所有行不去重性能更高。5.2 使用场景场景1合并两个相似的查询结果假设我们要生成一份“联系人”列表同时包含读者和作者假设有一个独立的 authors 表SELECTnameAScontact_name,读者AScontact_typeFROMreadersUNIONSELECTauthor,作者FROMbooksORDERBYcontact_name;场景2按条件拆分查询再合并查询“库存为0的图书”和“库存超过10的图书”作为两个极端情况展示SELECTtitle,stock,缺货ASstatusFROMbooksWHEREstock0UNIONALLSELECTtitle,stock,库存充足ASstatusFROMbooksWHEREstock10ORDERBYstock;这里使用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 结合子查询将所有“文学”类图书的库存加 1UPDATEbooksSETstockstock1WHEREidIN(SELECTbook_idFROMbook_category bcJOINcategories cONbc.category_idc.idWHEREc.name文学);6.3 DELETE 结合子查询删除没有任何借阅记录的读者DELETEFROMreadersWHEREidNOTIN(SELECTDISTINCTreader_idFROMborrow_records);或者用NOT EXISTS更安全DELETEFROMreaders rWHERENOTEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_idr.id);7. 实战综合运用让我们回到图书管理系统完成几个有挑战性的查询。7.1 查找从未借过书的读者两种方法对比方法一LEFT JOIN IS NULLSELECTr.nameFROMreaders rLEFTJOINborrow_records brONr.idbr.reader_idWHEREbr.idISNULL;方法二NOT EXISTSSELECTr.nameFROMreaders rWHERENOTEXISTS(SELECT1FROMborrow_records brWHEREbr.reader_idr.id);两种结果相同NOT EXISTS通常更直观地表达了“不存在”的语义且性能通常更好。7.2 找出借阅最活跃的读者借阅次数 所有读者的平均借阅次数使用派生表 标量子查询WITHreader_statsAS(SELECTreader_id,COUNT(*)AScntFROMborrow_recordsGROUPBYreader_id)SELECTr.name,rs.cntFROMreader_stats rsJOINreaders rONrs.reader_idr.idWHERErs.cnt(SELECTAVG(cnt)FROMreader_stats);这里引入了CTE公用表表达式MySQL 8.0 支持比嵌套派生表更清晰。CTE 作为WITH子句定义可被后续查询多次引用。7.3 合并查询生成“图书活跃度报告”同时展示被借次数最多的 3 本书和最少的 3 本书(SELECTtitle,COUNT(*)ASborrow_count,热门ASlabelFROMbooks bJOINborrow_records brONb.idbr.book_idGROUPBYb.id,b.titleORDERBYborrow_countDESCLIMIT3)UNIONALL(SELECTtitle,COUNT(*)ASborrow_count,冷门ASlabelFROMbooks bJOINborrow_records brONb.idbr.book_idGROUPBYb.id,b.titleORDERBYborrow_countASCLIMIT3)ORDERBYborrow_countDESC;7.4 查找借阅了所有“技术”类图书的读者这个需求比较高级需要关联子查询配合双重否定逻辑“借阅了所有技术类图书的读者”等价于“不存在一本技术类图书没有被该读者借阅过”。SELECTr.nameFROMreaders rWHERENOTEXISTS(SELECT1FROMbooks bJOINbook_category bcONb.idbc.book_idJOINcategories cONbc.category_idc.idWHEREc.name技术ANDNOTEXISTS(SELECT1FROMborrow_records brWHEREbr.book_idb.idANDbr.reader_idr.id));这个查询比较绕建议你放慢阅读最内层NOT EXISTS表示“该读者没有借过这本书”外层NOT EXISTS表示“不存在这样的技术书”——即“该读者借过所有技术书”。8. 小结本文我们深入了子查询和合并查询的高级用法子查询类型位置返回值常用操作符标量子查询WHERE / SELECT单个值,,列子查询WHERE一列多行IN,NOT IN,ANY,ALL行子查询WHERE一行多列 (col1, col2)派生表FROM多行多列作为临时表EXISTSWHERE布尔值EXISTS,NOT EXISTSEXISTS/NOT EXISTS是表达“存在/不存在”语义的首选性能通常优于IN/NOT IN且没有 NULL 陷阱。合并查询UNION/UNION ALL纵向拼接结果集注意列数和类型匹配。子查询可以与INSERT、UPDATE、DELETE结合实现基于其他表数据的增删改。现在你已经掌握了单个 SELECT 的几乎所有技能。下一个阶段我们将进入数据库的核心原理——索引、事务与 JDBC 编程让性能与安全再上一个台阶。在此之前别忘了完成第二阶段的最后一篇项目实战它将综合运用我们学过的所有知识思考题NOT IN有什么潜在陷阱用NOT EXISTS如何改写派生表和 CTE (WITH) 有什么区别分别在什么场景下使用UNION和JOIN的根本区别是什么它们能互相替代吗参考资料MySQL 8.0 Reference Manual - SubqueriesMySQL 8.0 Reference Manual - EXISTS and NOT EXISTSMySQL 8.0 Reference Manual - UNION