★ FEATURED ARTICLE 写SQL这事入门容易想写得利索却得跨过几道坎。我最近协助整理一套数据库教学示例回头把“简单查询、连接查询含自连接和子查询”这三类核心方式重新捋了一遍感触挺多很多同学不是不会写语法而是不清楚一条查询在执行时到底发生了什么以及什么场景该用哪一种。这篇文章我打算直接用示例对应例9.16到9.19那组数据把三种查询方式拆开讲透从语法结构、执行逻辑到适用场景最后附上自己踩过的坑。无论你是刚学完SQL基础的学生还是工作中经常写查询但总感觉差点意思的开发者这篇都能帮你把“会写”变成“会选”。先交代一下后面反复用到的模拟数据某高校教务库里有一张学生表students学号sid、姓名sname、性别gender、年龄age、院系dept、课程表courses课程号cid、课程名cname、学分credit、任课教师teacher和选课成绩表sc学号sid、课程号cid、成绩score。后面所有示例都在这三张表上跑方便对照。1. 三种查询方式到底在解决什么问题很多教材把简单查询、连接查询和子查询当作并列的三章但本质上它们是不同维度的东西。简单查询解决的是“在一张表里挑数据”连接查询解决的是“把多张表的行按照关系横向拼在一起”而子查询解决的是“把查询结果作为另一个查询的条件或数据来源”。理解这件事比死记语法重要得多。1.1 简单查询单表单条件的直球简单查询只有一个数据来源也就是FROM后面只有一个表。它干的事情无非是“选择行”和“投影列”WHERE负责把不满足条件的行过滤掉SELECT后面的列清单决定最终显示哪些字段。它能处理WHERE里的比较运算、逻辑组合、模糊匹配、范围判断也能配合ORDER BY排序、LIMIT控制行数。别小看它简单查询是所有复杂查询的底座。连接查询里每个表的过滤条件本质上还是简单查询的WHERE逻辑子查询内部的查询往往也是简单查询。我见过不少工作了几年的人写多表查询时喜欢把所有过滤都堆在最后结果数据量一大就慢得离谱根源就是没有把单表过滤先做好。1.2 连接查询多表横向拼数据连接查询解决的是“行与行之间的对应关系”。比如学生表和选课表一个学生可以选多门课选课表里一个sid能对应多条记录。想把学生的姓名和成绩单显示在同一行里就必须通过sid把students和sc连接起来。连接的本质是“笛卡尔积加过滤条件”先把两表所有行两两组合再根据连接条件保留匹配的行。连接方式里最常用的是内连接INNER JOIN和左外连接LEFT JOIN。自连接是连接查询里的特殊形态一张表和自己连接相当于把同一张表复制成两份分别起不同的别名再按某种关系匹配。它主要用来处理表内的层级关系比如员工表和经理都在同一张员工表里经理也是员工。1.3 子查询查询里再套查询子查询是嵌套在另一个查询内部的SELECT语句。它可以在WHERE子句里作为比较条件也可以在FROM子句里充当一张临时表还可以出现在SELECT列表里做标量计算。子查询的核心价值是“分步思考”先把复杂问题拆成一个个中间结果再让外层查询使用这些结果。子查询分两大类非相关子查询和相关子查询。非相关子查询的执行顺序是先内后外内层查询只执行一次结果拿给外层用相关子查询则要对外层每一行都执行一次因为内层查询引用了外层表的值。理解这个区别基本就理解了大半SQL性能问题。2. 简单查询的实操拆解例9.16带来的启发2.1 例9.16完整走查例9.16的需求是这样的查询计算机系年龄小于20岁的学生学号和姓名。对应的 SQL 是SELECT sid, sname FROM students WHERE dept 计算机系 AND age 20;这段查询的语法结构很规整SELECT指定输出列FROM指定数据来源WHERE指定行过滤条件。看上去很简单但执行逻辑值得说清楚。数据库不会先读SELECT而是先定位FROM students确定要扫描哪张表然后按WHERE条件逐行判断把满足“院系是计算机系”且“年龄小于20”的行留下最后才轮到SELECT从过滤后的行里取出sid和sname。这也就是为什么很多经验老到的开发者会强调“能早过滤就早过滤”。假如students表有十万行而计算机系只有五千行WHERE尽早把行数降下来后面排序、分组、连接的成本都会小很多。实际执行计划里优化器可能会调整顺序但从逻辑层面理解仍然是“先过滤后投影”。2.2 简单查询里容易被忽略的细节简单查询看着简单实际坑不少。先说NULL的问题。比如想查年龄不等于20岁的学生写成WHERE age 20结果会漏掉age为NULL的学生因为NULL参与比较时既不是真也不是假结果是不确定的“未知”。这时候要明确业务含义是想排除未知年龄还是保留如果保留得写成WHERE age 20 OR age IS NULL。再说SELECT *的坏习惯。例9.16只取两列但很多初学者喜欢SELECT *把不需要的字段全部捞出来。在测试环境无所谓在生产环境遇到大宽表时网络传输和内存开销都会放大。我的习惯是写查询前先想清楚“我要哪几列”一方面让结果更清爽另一方面也方便后续优化。还有一个容易忽略的点是排序的稳定性。如果想按年龄排序直接ORDER BY age没问题但当年龄相同的行很多时同一年龄内部的行序是不确定的。如果需要确定性结果就再加一个唯一字段做次级排序比如ORDER BY age, sid。这个经验在写分页查询、做数据对比时特别重要否则同一查询两次返回的行顺序可能对不上。3. 连接查询内连接、自连接与外连接的取舍3.1 内连接示例例9.17的拆解例9.17的需求是查询每个学生选修课程的成绩显示学生姓名、课程名和成绩。这个需求要同时用到三张表students里有姓名courses里有课程名sc里才有真正的选课关系。SQL 可以这么写SELECT stu.sname, cou.cname, sc.score FROM students AS stu JOIN sc ON stu.sid sc.sid JOIN courses AS cou ON sc.cid cou.cid;这里JOIN默认是内连接只保留两边都能匹配上的行。执行的逻辑是一步步来的先把students和sc做笛卡尔积用stu.sid sc.sid过滤出同一个学生的行组合再把结果和courses做笛卡尔积用sc.cid cou.cid过滤出课程名称对应的行。我经常跟人强调写连接查询时一定要先在纸上把表关系画出来。比如students和sc是一对多sc和courses是多对一连接条件是不是写在正确的关联字段上直接影响结果会不会失真。很多看起来“结果对了”的查询其实产生了大量重复行只是恰好没被发现。内连接最需要注意的是过滤条件放在WHERE和放在ON的区别。对于内连接两种写法结果往往一样因为WHERE会在连接结果上再过滤掉不匹配的行。但对于外连接这个区别就很关键放在ON里表示“连接时保留左边表的全部行然后匹配右边的行”放在WHERE里则会在连接完成后把不满足条件的行也删掉相当于把外连接降级成了内连接。3.2 自连接一张表当两张表用例9.18实战自连接是很多人第一次看到会觉得“这也能行”的查询。例9.18的场景是员工表emp字段包括员工编号eid、员工姓名ename、上级经理编号mgr_id。要查询每个员工以及他对应的经理姓名经理自己也在这张表里。SQL 写成SELECT e.ename AS 员工姓名, m.ename AS 经理姓名 FROM emp AS e LEFT JOIN emp AS m ON e.mgr_id m.eid;关键在于给同一张表起了两个别名e和m相当于把物理上的一张表在逻辑上当作两张独立的表来用。查询执行时左边扫描的是员工视角的数据右边扫描的是经理视角的数据通过e.mgr_id m.eid把员工记录里的经理编号和经理记录里的员工编号对上。这里我特意用了LEFT JOIN而不是JOIN因为公司里一般会有一个最高领导他的mgr_id是空的或者是自己。如果用内连接这个最高领导会直接被过滤掉不符合“查询所有员工及其经理”的需求。这是自连接里很容易犯的错没有想清楚到底要保留哪一侧的全量数据。自连接还可以做更多事情。比如查工资高于自己经理的员工JOIN emp m ON e.mgr_id m.eid WHERE e.salary m.salary比如查同一个院系里年龄比自己小的同学JOIN students s2 ON s1.dept s2.dept AND s1.age s2.age。这类“表内关联”需求用自连接是最直接的方案。3.3 外连接什么时候需要保留未匹配行内连接之外的左外连接、右外连接和全外连接核心思想是“保留哪一侧的全量数据”。左外连接保留左表的全部行右表的字段匹配不上时填NULL右外连接反之。全外连接则两侧都保留但很多数据库比如早期的版本不支持全外连接实际用得也少。什么时候必须用外连接举一个场景要统计每个学生的选课门数但有些学生可能一门课都没选。如果直接两表内连接再GROUP BY没选课的学生根本不会出现在结果里。这时候应该以students表为左表左外连接到sc表再计数。计数时要注意COUNT(sc.sid)会忽略NULL不会把没选课的学生算成1。外连接的执行逻辑也需要理解。它并不是先算内连接再把未匹配的行补上那么简单优化器的实现各有差异但你可以从逻辑上这样理解先完成行的匹配匹配不上的右侧字段置空然后保留左侧所有行。这个“置空”的操作意味着对右侧表字段的任何WHERE过滤都要小心一旦过滤条件落在右侧字段上保留的左表行又被删掉了。4. 子查询例9.19从嵌套到相关4.1 非相关子查询先内后外例9.19的第一个版本是查询成绩高于全校平均成绩的学生学号和成绩。这个需求可以拆成两步。第一步先算出全校平均分第二步找出所有成绩大于这个平均分的记录。用子查询一步写完SELECT sid, score FROM sc WHERE score ( SELECT AVG(score) FROM sc );括号里的SELECT AVG(score) FROM sc就是非相关子查询。它不依赖外层查询的任何字段所以执行顺序非常清晰数据库先执行内层子查询得到一个标量平均值再用这个值和外层每一行的score比较。内层只执行一次理解起来像是“先算好一个常数”。非相关子查询还有很多变体。比如查所有选了课程”数据库原理“的学生姓名先查出该课程的cid再用IN找学生。也可以把子查询放在FROM里当临时表比如先按课程分组算平均分再和sc表连接。这种“临时表”用法特别适合处理复杂的多层聚合因为它把中间结果显式地表达出来了可读性往往比一口气连接到底要好。4.2 相关子查询逐行执行的代价例9.19的第二个需求更聪明查询每门课程中成绩高于该课程平均分的学生。注意这里“高于平均分”不是跟全校比而是跟自己所在的课程比。用相关子查询可以这样写SELECT sc1.sid, sc1.cid, sc1.score FROM sc AS sc1 WHERE sc1.score ( SELECT AVG(sc2.score) FROM sc AS sc2 WHERE sc2.cid sc1.cid );内层子查询里出现了sc1.cid这个字段来自外层表。每次外层处理一行sc1都要把当前行的cid传进内层重新计算一次该课程的平均分。如果sc表有一万条选课记录相关子查询理论上要执行一万次内层查询性能自然比非相关子查询差得多。但相关子查询能表达很多非相关子查询写不出来的逻辑比如“找出比同部门平均工资高的员工”“找出成绩比自己所在课程平均分高的学生”。这类“跟同一组内的一部分做比较”的需求相关子查询是模板级的写法。使用时一定要确保子查询内部的连接条件能缩小范围比如sc2.cid sc1.cid就把内层扫描限制在同一个课程里配合索引可以勉强接受如果条件写成不带外键的那就容易变成全表扫描套全表扫描。4.3 子查询与连接查询的相互替代同一个需求既可以用子查询也可以用连接查询。比如“查询有选课记录的学生姓名”可以写成WHERE sid IN (SELECT sid FROM sc)也可以写成SELECT DISTINCT sname FROM students JOIN sc ON ...。两个方案结果一样但执行思路不同。子查询的表达更贴近人的思维先找有哪些学生选过课再去找这些学生。连接的表达更贴近数据的关系模型把关系横向展开再处理重复行。从性能角度看早年数据库对IN子查询优化不好很多人习惯于改成连接。现在的优化器已经越来越聪明很多情况下会等价改写。但我仍然建议当子查询在WHERE里且数据量很大时先看看执行计划再决定是否手动改写。实际工作里我见过太多“迷信连接永远比子查询快”的说法其实不一定。有些环境里相关子查询配合合适的索引反而比一堆连接更容易让优化器找到好路径。还有一个实用原则可读性和性能同样重要。团队协作时一段晦涩的循环子查询会让接手的人非常痛苦。如果子查询嵌套超过两层我通常会尽量把它拆成多个临时表或者用WITH公共表表达式让每一层只做一件事。这是维护性上的长期投资。5. 三种查询方式的适用场景与性能权衡5.1 一张速查表帮你快速决定我把自己多年来的选型经验整理成了下面这张表遇到需求可以直接对照需求特征推荐方式理由单表过滤、投影、排序简单查询结构最简单优化空间最大需要显示多张表的字段且关系明确内连接横向展开表达直接需要保留某侧未匹配的行如无选课记录的学生左/右外连接保留全量避免丢行同一张表内部有层级关系员工-经理自连接一张表当两张表用一个条件依赖一个聚合结果非相关子查询先算后比逻辑清晰每行的比较基准都不同如本课程平均分相关子查询可表达行内上下文复杂中层结果需要复用公共表表达式或子查询充当临时表提高可读性减少嵌套这张表只是参考。真正写的时候还要结合数据量、索引情况、团队习惯来定。没有绝对的最好只有当前场景下的最合适。5.2 性能考量连接顺序、索引与执行计划不管是简单查询、连接查询还是子查询性能的底层逻辑都差不多要扫描多少行、要处理多少中间结果、是否能用上索引。以连接查询为例多表连接时表的连接顺序会影响中间结果的大小。例如先连接students和sc通常比先连接sc和courses再用大表去连students更容易控制中间体积。优化器多数情况下能自动判断但复杂查询时还是需要人工干预比如用STRAIGHT_JOIN或调整FROM顺序调优。子查询的性能重点在于“内层查询的执行次数”。非相关子查询只执行一次相关子查询则取决于外层行数。因此写相关子查询前先问自己能不能拆成连接如果不能一定要确保内层子查询的WHERE条件走索引。比如前面例9.19的sc2.cid sc1.cid如果cid上有索引内层扫描就会明显加快。很多人忽略索引对连接查询的影响。连接条件字段上有索引驱动表每次拿一行去匹配被驱动表时就能走索引直接找到对应行没有索引数据库只能对被驱动表做全表扫描。所以好的设计不仅关心查询怎么写更关心连接字段、过滤字段是不是都建了合适的索引。5.3 可读性优先原则代码首先是给人看的其次才是给数据库执行的。我之前接手过一个项目里的“面条式”SQL五个子查询嵌套四层光括号对齐就花了半天。后来我按下面的思路重构先写简单查询验证各自的步骤然后用WITH把每步中间结果命名最后才把最终语句拼起来。这样既保留了子查询的灵活性又让每一步变得可测试、可调试。这个原则也适用于示例中的查询。比如例9.19那种相关子查询我会先跑内层确认平均分算得没问题再套外层。写复杂SQL和写程序代码类似尽量让每一步都有清晰的边界。6. 常见问题与避坑实录6.1 典型报错与解决写简单查询时最常见的报错是列名冲突。当一张表里有多个字段需要区分时不加别名会报“字段不明确”而当自连接时如果不给表起别名数据库根本不知道你引用的到底是哪一份表。解决方式就是养成给表起别名的习惯。FROM students AS stu并不会增加多少输入量但能避免很多低级错误。连接查询里最典型的错误是忘了写连接条件结果出现笛卡尔积爆炸。比如两张表各有1000行不带ON的内连接会得到100万行查询结果看着像是“重复了很多次”执行时间也长。这类问题排查时先看结果行数是否接近两表行数的乘积基本就能定位。子查询的报错里最常见的是“子查询返回多行”。比如WHERE score (SELECT AVG(score) FROM sc GROUP BY cid)这个子查询会对每个课程返回一个平均分返回多行就没法和一个score做等值比较。这时要么改成IN要么改成相关子查询或者把聚合结果作为临时表再连接。还有一类问题不报错但结果错NULL导致比较失效。比如WHERE mgr_id NULL这种写法永远查不到数据因为NULL不能用等号比较。自连接里要查没有经理的员工应该用e.mgr_id IS NULL而不是e.mgr_id NULL。6.2 从实战中养成的几个习惯我写 SQL 有几个固定习惯算是踩过不少坑之后总结出来的。第一每次写SELECT前先把结果列和表关系用自然语言描述出来再翻译成 SQL。比如“查询每个学生姓名和选课门数”这种描述先明确哪些表、哪些字段、分组粒度再动手。这个习惯帮我避免了很多“写完才发现要重写”的情况。第二写连接查询时先用小数据量验证连接条件是否正确。我经常把查询后面临时加一个LIMIT 50先看 50 行结果里有没有明显异常再放开全部数据。对生产库尤其重要一次全量错误查询可能拖垮数据库。第三相关子查询慢的时候先别急着改写成连接先看执行计划。很多时候慢的原因不是子查询本身而是缺失索引。给内层查询的关联字段和过滤字段建上索引后性能会有质的提升。这个经验我反复验证过很多次。第四也是最想提醒大家的SQL 写完之后一定用“业务角度”验证结果。比如查询“成绩高于平均分的学生”结果总数是不是在一个合理的范围有没有遗漏哪些课程没人选数据校验这步永远不能省特别是上线前或者给业务方出报表时。多问几个为什么比一次次返工要省时间得多。我自己刚开始写子查询时也总喜欢把所有功能塞进一条语句里显得水平很高。后来发现代码是写给人看的清晰比炫技重要。简单查询能解决就用简单查询一条连接能写清就别套子查询真正复杂的时候再上子查询和公共表表达式。这个“先简单后复杂”的原则让我少走很多弯路也推荐给每一个刚开始学习 SQL 的朋友。
阅读完成 · 觉得有帮助？
