做SQL查询开发这些年我见过最多的问题不是不会写SELECT而是写着写着就把自己绕进去了。明明一条语句能查出来的数据硬是拆成好几段在代码里拼明明一个自连接就能解决的问题非要在程序里循环遍历。所以这次我打算把SQL里最核心的三种查询方式——简单查询、连接查询、子查询——拿出来好好拆一遍。这三种方式不只是语法上的区别背后的执行逻辑、适用场景、踩坑点都不一样。不管你是刚入门的学生还是写了两三年业务代码的开发把这三块彻底吃透写复杂报表和业务查询的时候会顺手很多。先交代一下这篇内容适合谁看。如果你正在学数据库被各种JOIN和嵌套SELECT搞得头晕这篇能帮你把骨架搭起来。如果你已经工作了写SQL全靠百度拼凑这篇能帮你搞清楚每条语句到底怎么执行避免以后在性能问题上栽跟头。我会结合几个具体示例来展开重点讲清楚语法结构、执行逻辑和适用场景把我实际踩过的坑也一并交代了。1. 从整体看三种查询的定位与设计思路1.1 为什么把三种查询放在一起学很多人学SQL是零散着学的今天看到一条简单查询明天遇到一条多表连接后天又碰到一个嵌套子查询学是都学会了但脑子里没有体系。结果一碰到稍复杂的真实业务就开始犯难这需求到底该用连接还是子查询同样的结果为什么别人写的SQL跑得快我写的却把数据库拖卡了把三种查询放在一起对比理解核心目的是建立查询设计的全局观。简单查询是地基处理的是单表数据连接查询是横向扩展把多张表的数据拼成一张宽表子查询是纵向嵌套把一个查询的结果当作另一个查询的输入。这三种方式不是互相替代的关系而是在不同场景下各有优势。打个比方简单查询像你直接从书架上拿一本书连接查询像把两本书的内容按页码拼起来对照看子查询则像先看目录找到相关章节再去翻细节。从数据库角度来看这三种方式对应了不同的执行策略。简单查询走的是单表扫描数据库可以走索引也可以全表扫描逻辑最简单。连接查询要把多张表的数据做匹配数据库要么用嵌套循环连接Nested Loop Join要么用哈希连接Hash Join要么用排序合并连接Merge Join不同的表大小和数据分布会影响执行计划的选择。子查询则更灵活有的子查询会被改写成连接有的会独立执行一次再参与外层计算。理解了这层你就明白为什么有些写法快、有些写法慢。1.2 查询的统一执行顺序先有数据再谈条件大多数教科书教SQL的时候都是按SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY这个顺序来讲的导致很多人以为数据库也是按这个顺序执行。这就是很多问题的根源——你把WHERE里用别名当条件结果报错说列不存在其实不是列不存在是执行顺序里WHERE比SELECT早。真实的执行顺序是这样的先FROM确定从哪些表取数据再WHERE过滤行然后GROUP BY分组接着HAVING过滤分组再SELECT投影出需要的列最后ORDER BY排序。这个顺序虽然很多面试题里反复出现但真正写代码的时候能时刻记住的人并不多。我建议你把这个顺序抄下来贴在显示器边上写复杂查询的时候对着看少踩一半的坑。后面讲到的三种查询方式全都要在这条执行顺序的框架里理解。简单查询里的WHERE过滤连接查询里的ON条件和WHERE条件子查询里的外层和内层执行时机全都逃不出这个顺序。这句话先放在这里后面每个环节都能用上。1.3 一次完整的执行过程拆解我用最简单的一张表来说明。假设你有一个订单表orders字段有订单号、用户编号、金额、状态。你想查所有金额大于100元的已支付订单。SELECT 订单号, 金额 FROM orders WHERE 金额 100 AND 状态 已支付;这条语句的执行过程是这样的数据库先定位到orders表然后逐行扫描遇到金额大于100并且状态为已支付的行就保留下来其余的全部丢弃。过滤完成之后才轮到SELECT把订单号和金额这两列投影出来。注意这里的投射发生在过滤之后所以SELECT里算出来的字段WHERE里面是用不了的因为算的时候还没轮到它。这种拆解方式看起来简单但它是理解一切SQL执行逻辑的起点。自连接为什么要把一张表复制成两份子查询为什么内层先执行连接查询的ON条件为什么不加在WHERE里答案全在这个执行顺序的推导里。2. 简单查询基础过滤的语法与执行顺序2.1 SELECT与WHERE的职责分工简单查询本质上就是从一张表里按条件取数。它由三件事组成选哪些列SELECT从哪张表取FROM取哪些行WHERE。这里最容易混淆的是SELECT和WHERE的职责边界。SELECT负责的是垂直方向的裁剪决定你看到哪些列WHERE负责的是水平方向的裁剪决定你看到哪些行。举一个具体的例子还是订单表我要查状态为已支付的订单只需要订单号和金额列SELECT 订单号, 金额 FROM orders WHERE 状态 已支付;这句是典型的三段式简单查询。FROM锁定数据源WHERE把已支付的行筛出来SELECT把订单号和金额投到结果里。整个逻辑链非常直接。但实际操作中我见过很多人把过滤逻辑全堆在WHERE里一个查询恨不得写十个条件可读性和维护性都差。判断一个字段该放WHERE还是SELECT标准很简单你看它是在决定取哪些行还是在决定显示哪些列。前者进WHERE后者进SELECT。2.2 用示例拆解简单查询的完整步骤拿一个课堂里常见的示例来演示。假设这里有一个教学管理系统的数据库要从学生表student里查询所有非计算机专业的女生显示学号和姓名。这个需求可以拆成两步第一步是条件翻译专业字段不为计算机、性别为女这一步考验的是你把自然语言转成SQL条件的能力第二步是列选择输出学号和姓名。SELECT 学号, 姓名 FROM student WHERE 专业 ! 计算机 AND 性别 女;注意这里专业字段的写法我用的是不等于符号!。在实际的SQL方言里有些版本还支持用效果一样但团队代码里最好统一风格别混着写。还有一个常见的坑是专业不为计算机这个条件很容易被写成专业 非计算机这明显不对因为非计算机有好几个专业不能用等号硬凑。这条语句的执行逻辑很简单数据库先在student表里逐行扫描判断专业和性别两个字段同时满足条件的行才会被留下其他的全被过滤掉。这里有个效率细节如果表数据量很大频繁用不等于!做过滤是走不了索引的因为索引更适合等值匹配和范围匹配。这种查询在数据量小的时候感觉不出差异上百万行之后就明显变慢。2.3 简单查询的排序与分页细节简单查询除了过滤还经常要排序和分页。排序用ORDER BY分页在MySQL里用LIMIT在SQL Server里用TOP在Oracle里用ROWNUM。这里有一个容易忽略的点ORDER BY的执行顺序在SELECT之后所以你可以对SELECT里算出来的别名进行排序比如下面这条SELECT 学号, 姓名, 金额 * 0.8 AS 折扣价 FROM orders WHERE 状态 已支付 ORDER BY 折扣价 DESC;这条语句里折扣价这个别名是在SELECT阶段生成的ORDER BY阶段已经能拿到这个值了所以可以直接用它排序。很多人不知道这个细节非要在ORDER BY里把金额*0.8再写一遍结果SQL变得又长又难读。这个经验在实践中非常实用。分页这块有一个常见误区和性能问题。在MySQL里LIMIT的偏移量越大性能越差比如LIMIT 100000, 20数据库还是要先扫描前100000行再丢掉代价很高。如果是做业务报表数据量大的表尽量用条件过滤配合排序键来做而不是只靠偏移量硬翻。这也是简单查询里最容易忽略的性能点放到后面一起说。3. 连接查询从笛卡尔积到自连接3.1 连接查询的底层逻辑先拼再筛连接查询的本质是把两张表的数据按某种关联条件拼成一张临时的宽表。很多人学JOIN的时候直接背语法结果碰到实际问题不知道怎么写关联条件。我更喜欢先讲底层逻辑数据库在做连接的时候第一步做的是笛卡尔积也就是把左边表的每一行和右边表的每一行都组合一次得到一个巨大的中间结果第二步才用连接条件ON去筛掉那些不匹配的组合。举个例子学生表有10行选课表有20行那么没有连接条件的JOIN会产生200行结果其中绝大多数是没意义的组合。加上ON之后比如学生表的学号 选课表的学号那些学号对得上的行才会被留下。这个先拼再筛的过程是理解一切连接查询的钥匙。代价也要看清楚。如果两张表各有一百万行笛卡尔积中间结果就是一万亿行数据库显然不可能真的先生成这么庞大的中间结果再过滤实际执行的时候会用连接算法来优化。但对开发者来说理解这个概念仍然很重要因为它能帮你解释为什么连接查询写不好就会卡死也帮你理解为什么ON条件一定不能写错。3.2 内连接、左连接、右连接怎么选连接查询的语法印象里最核心的是JOIN关键字。内连接INNER JOIN只保留两边都匹配上的行左连接LEFT JOIN保留左边表的全部行右边没有匹配的就补NULL右连接RIGHT JOIN则相反。用一个课堂教学中常见的例子查每个学生的选课情况。学生表里有三个学生但只有一个学生实际选了课。内连接会只返回选课的那个学生左连接会把三个学生都列出来没选课的两个学生选课信息字段显示为NULL。这里的关键是你到底想查有选课记录的学生还是想查所有学生的选课情况。前者用内连接后者用左连接。实操中最常用的其实是内连接和左连接右连接用得很少因为大部分场景下把表的顺序换一下右连接就能改写成左连接。这里有一个我在项目里总结的习惯业务SQL里统一用LEFT JOIN不用RIGHT JOIN这样读代码的时候思路是从左往右顺的不用绕弯。团队协作的时候统一这个风格能省很多解释成本。SELECT s.学号, s.姓名, c.课程名 FROM student s LEFT JOIN course_selection c ON s.学号 c.学号;这段SQL展示了一个重要习惯给表起别名。s代表studentc代表course_selection这样写起来省事而且是多表查询时的规范。只要涉及两个或以上表所有字段前面都要带上表别名否则哪天两张表里都有一个学号字段数据库直接抛错说字段歧义。3.3 自连接一张表当作两张表用自连接当初学的时候是不少人的噩梦但它在某些场景里是真的绕不过去。什么叫自连接就是同一张表和自己做连接操作。一张表怎么能和自己连接关键思路是把它当作两张独立的表来用一个代表上级视角一个代表下级视角。最常见的例子是员工表里面有员工编号和上级编号上级其实也是员工也在同一张表里。我要查每个员工和他的上级名字如果不用自连接这个需求几乎没法用简单查询和普通连接完成。用自连接写是这样的SELECT e1.姓名 AS 员工姓名, e2.姓名 AS 上级姓名 FROM employee e1 LEFT JOIN employee e2 ON e1.上级编号 e2.员工编号;执行逻辑上数据库把employee表读了两遍一遍起名叫e1一遍起名叫e2然后像处理两张独立的表一样做连接。这里用LEFT JOIN是为了保证即使有人没有上级比如老板自己也照样出现在结果里上级姓名显示为NULL。我用一个课堂教学里的示例例9.17来说明会清楚一些。假设课程表course里有一列是先修课编号而先修课的详细信息也在course表里。如果要查每门课程和它的先修课程名称就必须自连接。思考的过程就是先从course表里取一条记录再从course表里去匹配那条先修课记录的课程名。因为没有第二张物理表只能把同一张表虚拟成两份。3.4 连接查询示例的完整实操过程把连接查询放到实际例子里操作一遍这里我参考的是一套教学系统中的示例约定两张表一张学生表student一张选课表sc里面保存学号、课程号和成绩。现在要查询所有选了课程编号为001的学生姓名和成绩。第一步是确认数据源查姓名在student表查成绩在sc表第二步是确认关联字段两张表靠学号关联第三步是确认连接类型因为只查选了课的学生所以用内连接第四步才是写SQL。SELECT s.姓名, sc.成绩 FROM student s INNER JOIN sc ON s.学号 sc.学号 WHERE sc.课程号 001;这里有一个特别容易踩的坑ON条件和WHERE条件的划分。ON负责连接关系学号相等WHERE负责业务过滤课程号是001。很多人会把课程号条件也写进ON里写成INNER JOIN sc ON s.学号 sc.学号 AND sc.课程号 001结果虽然一样但语义就混了。对内连接来说ON和WHERE都写在后面效果相同但换成LEFT JOIN结果就完全不一样了——写在ON里会保留左边全部行写在WHERE里会把不满足条件的行过滤掉。这个差异我碰到过无数次做报表的时候一不留神就出错。为什么大家都说连接查询是SQL的坎因为它要求你把数据关系想清楚。你在写之前头脑里必须先把表与表之间的关联链条画出来比如学生表通过学号关联选课表选课表通过课程号关联课程表你要查的结果横跨哪些表就顺着这条链走一遍连接条件一个都不能漏。3.5 连接查询的适用场景与性能直觉连接查询适合什么业务场景核心就是一张表的信息不够用的时候。订单表只有用户编号但想知道用户姓名就得连用户表成绩表只有课程号但想知道课程名就得连课程表。在实际的电商、教务、OA系统里几乎所有的报表都是多表连接拼出来的甚至要连七八张表才能拼出一个完整的数据视图。性能直觉也要建立起来。连接查询慢大部分时候不是数据库不行而是连接条件没走索引或者驱动表选错了。如果两张表各几十万行没有索引的等值连接相当于两层循环嵌套性能会很差。给关联字段加上索引之后数据库可以用索引快速定位匹配行性能能提升很多。简单总结一下连接查询的选型经验能用内连接就别用外连接因为外连接的结果集会更大有NULL填充后续处理容易出意外表的别名一定要用而且要有意义ON和WHERE的职责要分清楚这是连接查询写得好不好的分水岭。4. 子查询嵌套逻辑与三类场景4.1 子查询的本质与分类子查询就是嵌套在另一个查询里面的查询它在SQL里非常灵活可以出现在WHERE里、FROM里甚至SELECT里。很多人一看到子查询就头疼是因为分不清它到底什么时候执行、返回的是什么。我建议你先按返回结果的样子来分类理清楚这个写起来就顺了。子查询按返回结果分为三类标量子查询返回单个值比如查询某个学生的平均成绩行子查询返回一行多列比如返回一个学生的学号和姓名表子查询返回多行多列比如查所有高数成绩大于90分的学生列表。不同类型的子查询用在不同的语法位置上用的运算符也不一样标量的用等号大于小于多行多列的用IN、EXISTS这些。以标量子查询为例查一下比平均成绩高的学生有哪些就可以先查平均成绩再看每个学生的成绩。但实际写的时候不用分两步直接用嵌套写SELECT 学号, 成绩 FROM sc WHERE 成绩 ( SELECT AVG(成绩) FROM sc );这个括号里的SELECT就是标量子查询它先执行返回一个平均成绩的值然后外层查询拿这个值去做比较。整个逻辑非常直观。4.2 用示例拆解子查询的执行过程参考教学系统里的例9.19假设要查询选修了高等数学课程的学生姓名。这个需求有两种实现思路第一种是连接查询课程表、选课表、学生表三张表连起来第二种是子查询先用课程表确认课程编号再用选课表反查学生学号最后到学生表查姓名。子查询写起来思路是一层一层的SELECT 姓名 FROM student WHERE 学号 IN ( SELECT 学号 FROM sc WHERE 课程号 ( SELECT 课程号 FROM course WHERE 课程名 高等数学 ) );我来拆解这条嵌套查询的执行逻辑。最内层先执行在course表里找高等数学的课程号假设返回的是002。中间层用这个课程号到sc表里查所有选过这门课的学生学号得到一个结果集合比如是{2023001, 2023002}。外层再拿这个集合到student表里用IN去匹配最终返回两个学生的姓名。这个例子清楚展示了子查询的执行过程从内到外一层层解析。你在脑海里要有一个洋葱模型最里面的先执行结果往外传每一层都依赖内层的结果。用生活类比来说就像点外卖的时候先查商家再用商家编号查菜单最后用菜品编号下单每一步都是在上一步的结果范围里操作。4.3 相关子查询与非相关子查询一字之差千差万别子查询还有一个很重要的分类维度是否依赖外层查询。刚上面那个平均成绩的子查询它不依赖外层自己就能单独执行这叫非相关子查询。另一种相关子查询会引用外层查询的字段每次外层取一行内层就要执行一次相当于逐行查性能要格外注意。写到这必须把一个经典坑说透查询每门课程中成绩最高的学生的学号和成绩。这个需求,有人一上来就写GROUP BY结果发现查不出完整的学生信息。正确思路是相关子查询对每一行成绩记录判断它是否等于它所属课程的最高分。用法如下SELECT 学号, 课程号, 成绩 FROM sc s1 WHERE 成绩 ( SELECT MAX(成绩) FROM sc s2 WHERE s2.课程号 s1.课程号 );这个查询的可怕之处在于外层sc表有N行内层查询就要执行N次每一行都要去算一遍当前课程的最高分。数据量小的时候没问题数据量大了性能很难看。相关子查询不是不能用而是要慎用。我个人的判断标准是如果内层查询能用索引快速定位比如课程号上有索引逐行执行的代价还可控如果内层每次都全表扫描那直接改成连接查询或窗口函数会好得多。4.4 子查询与连接查询的选择边界很多人纠结同一个需求用子查询能写用连接也能写到底选哪个我给的参考标准是三条。第一条看可读性。逻辑层级清楚、嵌套不深的子查询读起来很顺比如查平均成绩以上的学生这种用子查询比用连接更直观。第二条看性能。子查询在某些数据库里执行计划可能被优化器改写成连接但相关子查询的性能要格外小心。第三条看业务表达。有的需求语义天然是先算出一个集合再匹配比如查所有选了课程的人用子查询更贴合思维有的需求语义是拼出一张结果表比如查订单连同用户信息用连接更自然。我见过一个常见的反面教材就是把三层嵌套的子查询写成一坨代码里根本看不懂逻辑后来改成连接查询两段SQL清晰明了。反过来说也有把一个简单的子查询场景硬改成连接结果LEFT JOIN把结果集扩大了出现重复数据。所以判断标准可以简化成一句话表的关系是靠关联字段连接还是靠值的集合匹配。前者倾向连接后者倾向子查询。另外有一个重要的优化小知识EXISTS和IN在某些场景下可以互换但语义上有差别。IN会先执行子查询把结果集缓存起来再和外层逐行比较EXISTS是相关子查询逐行检查是否存在一匹配上就停。当子查询结果集很大而外层行数很少时EXISTS通常更快反之子查询结果集很小、外层行数很大时IN更合适。这个结论不是绝对的不同数据库实现有差异但作为参考方向足够了。5. 常见问题与排查技巧实录5.1 连接查询的重复行与笛卡尔积爆炸连接查询最常见的Bug就是结果集莫名其妙多了很多行。之前有个做报表的同事查订单和订单明细以为只连一张表就够了结果发现销量翻了十倍一查才发现是因为同一张明细表里有多个商品他漏掉了明细的过滤条件导致一条订单匹配出全部商品行。排查思路其实很简单先去掉SELECT的列改成SELECT COUNT(*)再用连接前和连接后的行数做对比。如果连接后的行数和左表不一致基本可以判定是一对多连接导致的结果膨胀。解决方式是看业务上到底需要哪一层粒度如果只需要订单维度的汇总就要先按订单ID在明细里做聚合再连接订单表如果确实要明细的所有行那就要接受膨胀但过滤条件不能漏。笛卡尔积爆炸还要小心一种情况ON条件写错或者漏写。比如连着两张表ON写成了一张表内部的字段相等两张表的行就胡乱组合。我习惯写完JOIN之后先看一眼执行计划确认连接条件是不是真的用上了很多奇怪的数据问题瞬间就现原形了。5.2 子查询的NULL陷阱与NOT IN问题子查询里有一个非常隐蔽的坑NOT IN遇到NULL值会导致查询结果为空。举个具体例子要查没有选过任何课程的学生有人写SELECT 学号, 姓名 FROM student WHERE 学号 NOT IN ( SELECT 学号 FROM sc );如果sc表里恰好有一行记录的学号是NULL那这个NOT IN的结果就是空集一条数据都查不出来。原因是SQL的三值逻辑NULL参与比较时结果既不是TRUE也不是FALSE而是UNKNOWNNOT IN里面只要有一个NULL外面的判断就全部变成UNKNOWN一行都不返回。这个坑我在实际项目里踩过一次排查了很久才意识到是数据质量问题。解决办法很简单要么在子查询里加WHERE 学号 IS NOT NULL要么改用NOT EXISTS写法。NOT EXISTS不会触发三值逻辑语义也更符合逐行检查的思路所以我现在的习惯是遇到不存在这类业务直接用NOT EXISTS不在NOT IN上纠结。用一句口诀记看到NOT IN先问数据能不能保证非空没把握就换NOT EXISTS。5.3 判断一条SQL该不该优化的经验经常有人问我的SQL跑得慢怎么判断问题在哪。我给的排查动作是三步走。第一步看执行计划里有没有全表扫描尤其是不是在连接字段上做了全表扫如果有优先考虑加索引。第二步看有没有不必要的排序和临时表GROUP BY、DISTINCT、ORDER BY都会触发排序如果数据量很大排序代价很高可以想想业务上能不能接受去掉DISTINCT。第三步看是不是SQL逻辑本身的问题比如相关子查询嵌套过多或者LEFT JOIN结果集膨胀到了千万级别。有一个真实的系统慢查询案例排查后发现是一条子查询写在了循环里每次循环都启动一次数据库查询相当于N*N次查询。这种问题SQL优化救不了必须改代码把循环内的查询合并成一条带IN条件的大查询。想特别提醒一下初学者优化SQL的前提是你能看到执行计划。MySQL里用EXPLAINSQL Server看预估执行计划不同平台工具不一样但这步一定是排查的起点。没有执行计划的SQL性能分析全靠猜效率很低。本篇内容不涉及任何具体的数据库安装或配置过程但如果你正在用这些工具建议把执行计划功能摸熟。5.4 我个人的一些习惯和体会做了这么多年SQL开发我逐渐养成了几个固定习惯在这里一并分享。第一个习惯是写每条查询之前先在注释里写清楚业务需求。这样写完SQL之后回看时思路还在别人接手也好维护。第二个习惯是建索引的时候遵守选择性高的列优先原则比如状态字段只有几个固定值选择性很低单独建索引意义不大学号这种唯一值很多的对连接和过滤帮助极大。第三个习惯是写完连接查询之后先跑一次COUNT检查结果集行数是否符合预期确认无误再做后续操作。最后一个经验是关于自连接的。自连接在实际业务中出现的频率比你想象的高得多除了员工上级、课程先修课这种经典场景凡是表里有上下级含义的关联字段都要第一时间想到自连接。有时候一张表比你想的要丰富它能回答的问题远不止表象展示的那些列。回到本篇最开始的那个问题为什么有人写SQL很快有人写得很慢区别就在于他们脑子里有没有这套查询设计的全局观——先单表再连接再子查询最后还能判断出哪种写法才真正适合当前的业务和数据量。有了这个全局观面对任何一张新表你都不会怵。
做SQL查询开发这些年我见过最多的问题不是不会写SELECT而是写着写着就把自己绕进去了。明明一条语句能查出来的数据硬是拆成好几段在代码里拼明明一个自连接就能解决的问题非要在程序里循环遍历。所以这次我打算把SQL里最核心的三种查询方式——简单查询、连接查询、子查询——拿出来好好拆一遍。这三种方式不只是语法上的区别背后的执行逻辑、适用场景、踩坑点都不一样。不管你是刚入门的学生还是写了两三年业务代码的开发把这三块彻底吃透写复杂报表和业务查询的时候会顺手很多。先交代一下这篇内容适合谁看。如果你正在学数据库被各种JOIN和嵌套SELECT搞得头晕这篇能帮你把骨架搭起来。如果你已经工作了写SQL全靠百度拼凑这篇能帮你搞清楚每条语句到底怎么执行避免以后在性能问题上栽跟头。我会结合几个具体示例来展开重点讲清楚语法结构、执行逻辑和适用场景把我实际踩过的坑也一并交代了。1. 从整体看三种查询的定位与设计思路1.1 为什么把三种查询放在一起学很多人学SQL是零散着学的今天看到一条简单查询明天遇到一条多表连接后天又碰到一个嵌套子查询学是都学会了但脑子里没有体系。结果一碰到稍复杂的真实业务就开始犯难这需求到底该用连接还是子查询同样的结果为什么别人写的SQL跑得快我写的却把数据库拖卡了把三种查询放在一起对比理解核心目的是建立查询设计的全局观。简单查询是地基处理的是单表数据连接查询是横向扩展把多张表的数据拼成一张宽表子查询是纵向嵌套把一个查询的结果当作另一个查询的输入。这三种方式不是互相替代的关系而是在不同场景下各有优势。打个比方简单查询像你直接从书架上拿一本书连接查询像把两本书的内容按页码拼起来对照看子查询则像先看目录找到相关章节再去翻细节。从数据库角度来看这三种方式对应了不同的执行策略。简单查询走的是单表扫描数据库可以走索引也可以全表扫描逻辑最简单。连接查询要把多张表的数据做匹配数据库要么用嵌套循环连接Nested Loop Join要么用哈希连接Hash Join要么用排序合并连接Merge Join不同的表大小和数据分布会影响执行计划的选择。子查询则更灵活有的子查询会被改写成连接有的会独立执行一次再参与外层计算。理解了这层你就明白为什么有些写法快、有些写法慢。1.2 查询的统一执行顺序先有数据再谈条件大多数教科书教SQL的时候都是按SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY这个顺序来讲的导致很多人以为数据库也是按这个顺序执行。这就是很多问题的根源——你把WHERE里用别名当条件结果报错说列不存在其实不是列不存在是执行顺序里WHERE比SELECT早。真实的执行顺序是这样的先FROM确定从哪些表取数据再WHERE过滤行然后GROUP BY分组接着HAVING过滤分组再SELECT投影出需要的列最后ORDER BY排序。这个顺序虽然很多面试题里反复出现但真正写代码的时候能时刻记住的人并不多。我建议你把这个顺序抄下来贴在显示器边上写复杂查询的时候对着看少踩一半的坑。后面讲到的三种查询方式全都要在这条执行顺序的框架里理解。简单查询里的WHERE过滤连接查询里的ON条件和WHERE条件子查询里的外层和内层执行时机全都逃不出这个顺序。这句话先放在这里后面每个环节都能用上。1.3 一次完整的执行过程拆解我用最简单的一张表来说明。假设你有一个订单表orders字段有订单号、用户编号、金额、状态。你想查所有金额大于100元的已支付订单。SELECT 订单号, 金额 FROM orders WHERE 金额 100 AND 状态 已支付;这条语句的执行过程是这样的数据库先定位到orders表然后逐行扫描遇到金额大于100并且状态为已支付的行就保留下来其余的全部丢弃。过滤完成之后才轮到SELECT把订单号和金额这两列投影出来。注意这里的投射发生在过滤之后所以SELECT里算出来的字段WHERE里面是用不了的因为算的时候还没轮到它。这种拆解方式看起来简单但它是理解一切SQL执行逻辑的起点。自连接为什么要把一张表复制成两份子查询为什么内层先执行连接查询的ON条件为什么不加在WHERE里答案全在这个执行顺序的推导里。2. 简单查询基础过滤的语法与执行顺序2.1 SELECT与WHERE的职责分工简单查询本质上就是从一张表里按条件取数。它由三件事组成选哪些列SELECT从哪张表取FROM取哪些行WHERE。这里最容易混淆的是SELECT和WHERE的职责边界。SELECT负责的是垂直方向的裁剪决定你看到哪些列WHERE负责的是水平方向的裁剪决定你看到哪些行。举一个具体的例子还是订单表我要查状态为已支付的订单只需要订单号和金额列SELECT 订单号, 金额 FROM orders WHERE 状态 已支付;这句是典型的三段式简单查询。FROM锁定数据源WHERE把已支付的行筛出来SELECT把订单号和金额投到结果里。整个逻辑链非常直接。但实际操作中我见过很多人把过滤逻辑全堆在WHERE里一个查询恨不得写十个条件可读性和维护性都差。判断一个字段该放WHERE还是SELECT标准很简单你看它是在决定取哪些行还是在决定显示哪些列。前者进WHERE后者进SELECT。2.2 用示例拆解简单查询的完整步骤拿一个课堂里常见的示例来演示。假设这里有一个教学管理系统的数据库要从学生表student里查询所有非计算机专业的女生显示学号和姓名。这个需求可以拆成两步第一步是条件翻译专业字段不为计算机、性别为女这一步考验的是你把自然语言转成SQL条件的能力第二步是列选择输出学号和姓名。SELECT 学号, 姓名 FROM student WHERE 专业 ! 计算机 AND 性别 女;注意这里专业字段的写法我用的是不等于符号!。在实际的SQL方言里有些版本还支持用效果一样但团队代码里最好统一风格别混着写。还有一个常见的坑是专业不为计算机这个条件很容易被写成专业 非计算机这明显不对因为非计算机有好几个专业不能用等号硬凑。这条语句的执行逻辑很简单数据库先在student表里逐行扫描判断专业和性别两个字段同时满足条件的行才会被留下其他的全被过滤掉。这里有个效率细节如果表数据量很大频繁用不等于!做过滤是走不了索引的因为索引更适合等值匹配和范围匹配。这种查询在数据量小的时候感觉不出差异上百万行之后就明显变慢。2.3 简单查询的排序与分页细节简单查询除了过滤还经常要排序和分页。排序用ORDER BY分页在MySQL里用LIMIT在SQL Server里用TOP在Oracle里用ROWNUM。这里有一个容易忽略的点ORDER BY的执行顺序在SELECT之后所以你可以对SELECT里算出来的别名进行排序比如下面这条SELECT 学号, 姓名, 金额 * 0.8 AS 折扣价 FROM orders WHERE 状态 已支付 ORDER BY 折扣价 DESC;这条语句里折扣价这个别名是在SELECT阶段生成的ORDER BY阶段已经能拿到这个值了所以可以直接用它排序。很多人不知道这个细节非要在ORDER BY里把金额*0.8再写一遍结果SQL变得又长又难读。这个经验在实践中非常实用。分页这块有一个常见误区和性能问题。在MySQL里LIMIT的偏移量越大性能越差比如LIMIT 100000, 20数据库还是要先扫描前100000行再丢掉代价很高。如果是做业务报表数据量大的表尽量用条件过滤配合排序键来做而不是只靠偏移量硬翻。这也是简单查询里最容易忽略的性能点放到后面一起说。3. 连接查询从笛卡尔积到自连接3.1 连接查询的底层逻辑先拼再筛连接查询的本质是把两张表的数据按某种关联条件拼成一张临时的宽表。很多人学JOIN的时候直接背语法结果碰到实际问题不知道怎么写关联条件。我更喜欢先讲底层逻辑数据库在做连接的时候第一步做的是笛卡尔积也就是把左边表的每一行和右边表的每一行都组合一次得到一个巨大的中间结果第二步才用连接条件ON去筛掉那些不匹配的组合。举个例子学生表有10行选课表有20行那么没有连接条件的JOIN会产生200行结果其中绝大多数是没意义的组合。加上ON之后比如学生表的学号 选课表的学号那些学号对得上的行才会被留下。这个先拼再筛的过程是理解一切连接查询的钥匙。代价也要看清楚。如果两张表各有一百万行笛卡尔积中间结果就是一万亿行数据库显然不可能真的先生成这么庞大的中间结果再过滤实际执行的时候会用连接算法来优化。但对开发者来说理解这个概念仍然很重要因为它能帮你解释为什么连接查询写不好就会卡死也帮你理解为什么ON条件一定不能写错。3.2 内连接、左连接、右连接怎么选连接查询的语法印象里最核心的是JOIN关键字。内连接INNER JOIN只保留两边都匹配上的行左连接LEFT JOIN保留左边表的全部行右边没有匹配的就补NULL右连接RIGHT JOIN则相反。用一个课堂教学中常见的例子查每个学生的选课情况。学生表里有三个学生但只有一个学生实际选了课。内连接会只返回选课的那个学生左连接会把三个学生都列出来没选课的两个学生选课信息字段显示为NULL。这里的关键是你到底想查有选课记录的学生还是想查所有学生的选课情况。前者用内连接后者用左连接。实操中最常用的其实是内连接和左连接右连接用得很少因为大部分场景下把表的顺序换一下右连接就能改写成左连接。这里有一个我在项目里总结的习惯业务SQL里统一用LEFT JOIN不用RIGHT JOIN这样读代码的时候思路是从左往右顺的不用绕弯。团队协作的时候统一这个风格能省很多解释成本。SELECT s.学号, s.姓名, c.课程名 FROM student s LEFT JOIN course_selection c ON s.学号 c.学号;这段SQL展示了一个重要习惯给表起别名。s代表studentc代表course_selection这样写起来省事而且是多表查询时的规范。只要涉及两个或以上表所有字段前面都要带上表别名否则哪天两张表里都有一个学号字段数据库直接抛错说字段歧义。3.3 自连接一张表当作两张表用自连接当初学的时候是不少人的噩梦但它在某些场景里是真的绕不过去。什么叫自连接就是同一张表和自己做连接操作。一张表怎么能和自己连接关键思路是把它当作两张独立的表来用一个代表上级视角一个代表下级视角。最常见的例子是员工表里面有员工编号和上级编号上级其实也是员工也在同一张表里。我要查每个员工和他的上级名字如果不用自连接这个需求几乎没法用简单查询和普通连接完成。用自连接写是这样的SELECT e1.姓名 AS 员工姓名, e2.姓名 AS 上级姓名 FROM employee e1 LEFT JOIN employee e2 ON e1.上级编号 e2.员工编号;执行逻辑上数据库把employee表读了两遍一遍起名叫e1一遍起名叫e2然后像处理两张独立的表一样做连接。这里用LEFT JOIN是为了保证即使有人没有上级比如老板自己也照样出现在结果里上级姓名显示为NULL。我用一个课堂教学里的示例例9.17来说明会清楚一些。假设课程表course里有一列是先修课编号而先修课的详细信息也在course表里。如果要查每门课程和它的先修课程名称就必须自连接。思考的过程就是先从course表里取一条记录再从course表里去匹配那条先修课记录的课程名。因为没有第二张物理表只能把同一张表虚拟成两份。3.4 连接查询示例的完整实操过程把连接查询放到实际例子里操作一遍这里我参考的是一套教学系统中的示例约定两张表一张学生表student一张选课表sc里面保存学号、课程号和成绩。现在要查询所有选了课程编号为001的学生姓名和成绩。第一步是确认数据源查姓名在student表查成绩在sc表第二步是确认关联字段两张表靠学号关联第三步是确认连接类型因为只查选了课的学生所以用内连接第四步才是写SQL。SELECT s.姓名, sc.成绩 FROM student s INNER JOIN sc ON s.学号 sc.学号 WHERE sc.课程号 001;这里有一个特别容易踩的坑ON条件和WHERE条件的划分。ON负责连接关系学号相等WHERE负责业务过滤课程号是001。很多人会把课程号条件也写进ON里写成INNER JOIN sc ON s.学号 sc.学号 AND sc.课程号 001结果虽然一样但语义就混了。对内连接来说ON和WHERE都写在后面效果相同但换成LEFT JOIN结果就完全不一样了——写在ON里会保留左边全部行写在WHERE里会把不满足条件的行过滤掉。这个差异我碰到过无数次做报表的时候一不留神就出错。为什么大家都说连接查询是SQL的坎因为它要求你把数据关系想清楚。你在写之前头脑里必须先把表与表之间的关联链条画出来比如学生表通过学号关联选课表选课表通过课程号关联课程表你要查的结果横跨哪些表就顺着这条链走一遍连接条件一个都不能漏。3.5 连接查询的适用场景与性能直觉连接查询适合什么业务场景核心就是一张表的信息不够用的时候。订单表只有用户编号但想知道用户姓名就得连用户表成绩表只有课程号但想知道课程名就得连课程表。在实际的电商、教务、OA系统里几乎所有的报表都是多表连接拼出来的甚至要连七八张表才能拼出一个完整的数据视图。性能直觉也要建立起来。连接查询慢大部分时候不是数据库不行而是连接条件没走索引或者驱动表选错了。如果两张表各几十万行没有索引的等值连接相当于两层循环嵌套性能会很差。给关联字段加上索引之后数据库可以用索引快速定位匹配行性能能提升很多。简单总结一下连接查询的选型经验能用内连接就别用外连接因为外连接的结果集会更大有NULL填充后续处理容易出意外表的别名一定要用而且要有意义ON和WHERE的职责要分清楚这是连接查询写得好不好的分水岭。4. 子查询嵌套逻辑与三类场景4.1 子查询的本质与分类子查询就是嵌套在另一个查询里面的查询它在SQL里非常灵活可以出现在WHERE里、FROM里甚至SELECT里。很多人一看到子查询就头疼是因为分不清它到底什么时候执行、返回的是什么。我建议你先按返回结果的样子来分类理清楚这个写起来就顺了。子查询按返回结果分为三类标量子查询返回单个值比如查询某个学生的平均成绩行子查询返回一行多列比如返回一个学生的学号和姓名表子查询返回多行多列比如查所有高数成绩大于90分的学生列表。不同类型的子查询用在不同的语法位置上用的运算符也不一样标量的用等号大于小于多行多列的用IN、EXISTS这些。以标量子查询为例查一下比平均成绩高的学生有哪些就可以先查平均成绩再看每个学生的成绩。但实际写的时候不用分两步直接用嵌套写SELECT 学号, 成绩 FROM sc WHERE 成绩 ( SELECT AVG(成绩) FROM sc );这个括号里的SELECT就是标量子查询它先执行返回一个平均成绩的值然后外层查询拿这个值去做比较。整个逻辑非常直观。4.2 用示例拆解子查询的执行过程参考教学系统里的例9.19假设要查询选修了高等数学课程的学生姓名。这个需求有两种实现思路第一种是连接查询课程表、选课表、学生表三张表连起来第二种是子查询先用课程表确认课程编号再用选课表反查学生学号最后到学生表查姓名。子查询写起来思路是一层一层的SELECT 姓名 FROM student WHERE 学号 IN ( SELECT 学号 FROM sc WHERE 课程号 ( SELECT 课程号 FROM course WHERE 课程名 高等数学 ) );我来拆解这条嵌套查询的执行逻辑。最内层先执行在course表里找高等数学的课程号假设返回的是002。中间层用这个课程号到sc表里查所有选过这门课的学生学号得到一个结果集合比如是{2023001, 2023002}。外层再拿这个集合到student表里用IN去匹配最终返回两个学生的姓名。这个例子清楚展示了子查询的执行过程从内到外一层层解析。你在脑海里要有一个洋葱模型最里面的先执行结果往外传每一层都依赖内层的结果。用生活类比来说就像点外卖的时候先查商家再用商家编号查菜单最后用菜品编号下单每一步都是在上一步的结果范围里操作。4.3 相关子查询与非相关子查询一字之差千差万别子查询还有一个很重要的分类维度是否依赖外层查询。刚上面那个平均成绩的子查询它不依赖外层自己就能单独执行这叫非相关子查询。另一种相关子查询会引用外层查询的字段每次外层取一行内层就要执行一次相当于逐行查性能要格外注意。写到这必须把一个经典坑说透查询每门课程中成绩最高的学生的学号和成绩。这个需求,有人一上来就写GROUP BY结果发现查不出完整的学生信息。正确思路是相关子查询对每一行成绩记录判断它是否等于它所属课程的最高分。用法如下SELECT 学号, 课程号, 成绩 FROM sc s1 WHERE 成绩 ( SELECT MAX(成绩) FROM sc s2 WHERE s2.课程号 s1.课程号 );这个查询的可怕之处在于外层sc表有N行内层查询就要执行N次每一行都要去算一遍当前课程的最高分。数据量小的时候没问题数据量大了性能很难看。相关子查询不是不能用而是要慎用。我个人的判断标准是如果内层查询能用索引快速定位比如课程号上有索引逐行执行的代价还可控如果内层每次都全表扫描那直接改成连接查询或窗口函数会好得多。4.4 子查询与连接查询的选择边界很多人纠结同一个需求用子查询能写用连接也能写到底选哪个我给的参考标准是三条。第一条看可读性。逻辑层级清楚、嵌套不深的子查询读起来很顺比如查平均成绩以上的学生这种用子查询比用连接更直观。第二条看性能。子查询在某些数据库里执行计划可能被优化器改写成连接但相关子查询的性能要格外小心。第三条看业务表达。有的需求语义天然是先算出一个集合再匹配比如查所有选了课程的人用子查询更贴合思维有的需求语义是拼出一张结果表比如查订单连同用户信息用连接更自然。我见过一个常见的反面教材就是把三层嵌套的子查询写成一坨代码里根本看不懂逻辑后来改成连接查询两段SQL清晰明了。反过来说也有把一个简单的子查询场景硬改成连接结果LEFT JOIN把结果集扩大了出现重复数据。所以判断标准可以简化成一句话表的关系是靠关联字段连接还是靠值的集合匹配。前者倾向连接后者倾向子查询。另外有一个重要的优化小知识EXISTS和IN在某些场景下可以互换但语义上有差别。IN会先执行子查询把结果集缓存起来再和外层逐行比较EXISTS是相关子查询逐行检查是否存在一匹配上就停。当子查询结果集很大而外层行数很少时EXISTS通常更快反之子查询结果集很小、外层行数很大时IN更合适。这个结论不是绝对的不同数据库实现有差异但作为参考方向足够了。5. 常见问题与排查技巧实录5.1 连接查询的重复行与笛卡尔积爆炸连接查询最常见的Bug就是结果集莫名其妙多了很多行。之前有个做报表的同事查订单和订单明细以为只连一张表就够了结果发现销量翻了十倍一查才发现是因为同一张明细表里有多个商品他漏掉了明细的过滤条件导致一条订单匹配出全部商品行。排查思路其实很简单先去掉SELECT的列改成SELECT COUNT(*)再用连接前和连接后的行数做对比。如果连接后的行数和左表不一致基本可以判定是一对多连接导致的结果膨胀。解决方式是看业务上到底需要哪一层粒度如果只需要订单维度的汇总就要先按订单ID在明细里做聚合再连接订单表如果确实要明细的所有行那就要接受膨胀但过滤条件不能漏。笛卡尔积爆炸还要小心一种情况ON条件写错或者漏写。比如连着两张表ON写成了一张表内部的字段相等两张表的行就胡乱组合。我习惯写完JOIN之后先看一眼执行计划确认连接条件是不是真的用上了很多奇怪的数据问题瞬间就现原形了。5.2 子查询的NULL陷阱与NOT IN问题子查询里有一个非常隐蔽的坑NOT IN遇到NULL值会导致查询结果为空。举个具体例子要查没有选过任何课程的学生有人写SELECT 学号, 姓名 FROM student WHERE 学号 NOT IN ( SELECT 学号 FROM sc );如果sc表里恰好有一行记录的学号是NULL那这个NOT IN的结果就是空集一条数据都查不出来。原因是SQL的三值逻辑NULL参与比较时结果既不是TRUE也不是FALSE而是UNKNOWNNOT IN里面只要有一个NULL外面的判断就全部变成UNKNOWN一行都不返回。这个坑我在实际项目里踩过一次排查了很久才意识到是数据质量问题。解决办法很简单要么在子查询里加WHERE 学号 IS NOT NULL要么改用NOT EXISTS写法。NOT EXISTS不会触发三值逻辑语义也更符合逐行检查的思路所以我现在的习惯是遇到不存在这类业务直接用NOT EXISTS不在NOT IN上纠结。用一句口诀记看到NOT IN先问数据能不能保证非空没把握就换NOT EXISTS。5.3 判断一条SQL该不该优化的经验经常有人问我的SQL跑得慢怎么判断问题在哪。我给的排查动作是三步走。第一步看执行计划里有没有全表扫描尤其是不是在连接字段上做了全表扫如果有优先考虑加索引。第二步看有没有不必要的排序和临时表GROUP BY、DISTINCT、ORDER BY都会触发排序如果数据量很大排序代价很高可以想想业务上能不能接受去掉DISTINCT。第三步看是不是SQL逻辑本身的问题比如相关子查询嵌套过多或者LEFT JOIN结果集膨胀到了千万级别。有一个真实的系统慢查询案例排查后发现是一条子查询写在了循环里每次循环都启动一次数据库查询相当于N*N次查询。这种问题SQL优化救不了必须改代码把循环内的查询合并成一条带IN条件的大查询。想特别提醒一下初学者优化SQL的前提是你能看到执行计划。MySQL里用EXPLAINSQL Server看预估执行计划不同平台工具不一样但这步一定是排查的起点。没有执行计划的SQL性能分析全靠猜效率很低。本篇内容不涉及任何具体的数据库安装或配置过程但如果你正在用这些工具建议把执行计划功能摸熟。5.4 我个人的一些习惯和体会做了这么多年SQL开发我逐渐养成了几个固定习惯在这里一并分享。第一个习惯是写每条查询之前先在注释里写清楚业务需求。这样写完SQL之后回看时思路还在别人接手也好维护。第二个习惯是建索引的时候遵守选择性高的列优先原则比如状态字段只有几个固定值选择性很低单独建索引意义不大学号这种唯一值很多的对连接和过滤帮助极大。第三个习惯是写完连接查询之后先跑一次COUNT检查结果集行数是否符合预期确认无误再做后续操作。最后一个经验是关于自连接的。自连接在实际业务中出现的频率比你想象的高得多除了员工上级、课程先修课这种经典场景凡是表里有上下级含义的关联字段都要第一时间想到自连接。有时候一张表比你想的要丰富它能回答的问题远不止表象展示的那些列。回到本篇最开始的那个问题为什么有人写SQL很快有人写得很慢区别就在于他们脑子里有没有这套查询设计的全局观——先单表再连接再子查询最后还能判断出哪种写法才真正适合当前的业务和数据量。有了这个全局观面对任何一张新表你都不会怵。
