数据库实验【6】这期做连接查询。说实话很多初学者在学到SQL基础操作的时候都挺自信SELECT、INSERT、UPDATE、DELETE用得很顺一到JOIN就开始犯迷糊。为什么因为前面的操作都是针对一张表连接查询一下子把多张表搅在一起结果集怎么来的、为什么多出那么多行、为什么有些字段是空的这些问题不亲手做几轮实验光看书很难真正理解。这篇博文就从实验准备、建表造数开始把内连接、外连接、交叉连接、自连接全部跑一遍每个SQL都给出执行结果解释再把我做实验时踩过的坑整理出来。适合正在学数据库的在校学生也适合自学SQL准备做数据分析的朋友。哪怕你平时已经能写JOIN我也建议你看看最后那部分常见问题很可能碰到你之前没想明白的场景。1. 连接查询为什么值得单独开一期实验1.1 关系型数据库的“拆”与“连”关系型数据库和Excel最大的区别就是数据会被“拆”到不同表里。这个拆不是乱拆而是按范式规则来拆。第一范式要求列不可再分第二范式消除部分依赖第三范式消除传递依赖。这样设计的结果就是一张表只保存一个主题的数据冗余大幅减少更新数据时也不会出现改一处漏一处的尴尬。但拆带来的副作用也很明显想要还原一条完整信息往往需要把多张表重新拼起来。比如你要查“某个学生选了哪些课、每门课多少分”学生信息在student表选课记录在sc表课程信息在course表单靠单表查询根本拿不到完整答案。这时候就轮到连接查询出场。它做的事情用一句话就可以讲明白按照指定关联条件把多张表的行匹配起来拼出一行更完整的记录。如果说建表阶段是在规划“货物分别放在哪几个仓库”那连接查询就是按照出库单把不同仓库的货物装进同一个箱子。拆得越规范拼的时候就越依赖JOIN这也是为什么连接查询会成为数据库课程实验里绕不开的固定环节。1.2 连接类型一次性分清连接类型很多名字也容易混淆。我习惯用一张表把它们放到一起看连接类型一句话作用结果集特征INNER JOIN只保留两边都能匹配上的行两边匹配成功的交集LEFT JOIN左表全保留右表匹配不上补NULL左表全集RIGHT JOIN右表全保留左表匹配不上补NULL右表全集FULL JOIN两边都全保留匹配不上补NULL左右两表的并集CROSS JOIN不问条件两两配对笛卡尔积自连接表和自己按规则匹配依具体条件而定这几种连接看着名字不少其实核心就两个词“保留哪边”和“要不要条件”。内连接两边都不偏袒严格取交集外连接以某一边为基准另一边能匹配上就匹配上匹配不上就用NULL占位交叉连接干脆不问条件直接两两配对结果行数是两边行数的乘积。只要把“以谁为主”想清楚外连接基本就不会搞错方向。1.3 连接查询和子查询怎么选很多人问能用子查询解决的事为什么要写JOIN我个人的习惯是如果最终要输出多张表的字段直接用JOIN如果只是做“存在性判断”或者“条件过滤”子查询会更简洁。比如“查成绩超过平均分的学生”子查询写起来非常自然但“查每个学生的选课明细”子查询反而会把SQL写得又长又乱。另外要明白一件事数据库优化器在处理子查询时很多时候也会自动改写成连接查询来执行。所以两者在实际运行层面并不是完全对立的。初学阶段把连接查询练扎实对理解执行计划、排查慢查询都特别有帮助。2. 实验数据准备先把测试环境搭明白2.1 表结构设计思路这次实验我用的是某高校教务系统里最常见的三张表学生表student、课程表course、选课表sc。选课表是典型的关系表只存学号和课程号加上成绩专门用来表达“学生和课程的多对多关系”。这个设计思路很关键一个学生可以选多门课一门课可以有多名学生选这种多对多关系在关系模型里必须拆成两个一对多关系sc表就是连接两张主表的桥。建表SQL如下CREATE TABLE student ( sno CHAR(8) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2), sage INT, sdept VARCHAR(20) ); CREATE TABLE course ( cno CHAR(3) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit INT, pre_cno CHAR(3) ); CREATE TABLE sc ( sno CHAR(8), cno CHAR(3), grade DECIMAL(5,1), PRIMARY KEY(sno, cno), FOREIGN KEY(sno) REFERENCES student(sno), FOREIGN KEY(cno) REFERENCES course(cno) );这里有两个细节值得说明。第一个是sc表用了复合主键(sno, cno)目的是防止同一学生对同一课程出现重复选课记录这是关系表设计中很常见的做法。第二个是course表里加了一个pre_cno字段表示先修课程号这样后面做自连接时有真正的业务语义可讲而不是为了连接而连接。2.2 测试数据与边界设计空表没法做实验我先插入一批数据。这次故意让数据带点“不整齐”的地方因为连接查询实验最有价值的观察点就是匹配不上的时候结果会变成什么样。INSERT INTO student VALUES (2018001,小张,男,20,计算机系); INSERT INTO student VALUES (2018002,小李,女,19,计算机系); INSERT INTO student VALUES (2018003,小王,男,21,数学系); INSERT INTO student VALUES (2018004,小赵,女,20,外语系); INSERT INTO student VALUES (2018005,小孙,男,20,数学系); INSERT INTO course VALUES (C1,数据库原理,4,NULL); INSERT INTO course VALUES (C2,数据结构,3,C1); INSERT INTO course VALUES (C3,操作系统,3,C2); INSERT INTO sc VALUES (2018001,C1,85); INSERT INTO sc VALUES (2018001,C2,92); INSERT INTO sc VALUES (2018002,C1,76); INSERT INTO sc VALUES (2018003,C3,66); INSERT INTO sc VALUES (2018004,C1,90); INSERT INTO sc VALUES (2018004,C3,88);数据里特意埋了几个边界小张选了两门课小李只选了一门小王和小赵各选了一门但课程不同C2课程只有小张一个人选小孙干脆一门课都没选。这样的分布能让外连接时有足够的NULL填充现象可以观察如果每个人都选满课、每门课都有人选那各种连接查询结果看起来都差不多实验也就失去了意义。插入数据时最容易踩的坑是外键约束顺序。如果先插sc表再插student表数据库会直接报外键错误。所以建表和造数的固定顺序是先主表再从表这个顺序也是后续所有多表操作的基本纪律。3. 五种连接查询的完整实验与SQL拆解3.1 INNER JOIN只保留两边都匹配的行内连接是连接查询里最基础的写法语义就是“只要两边匹配成功的记录”。实验里我查的是所有选课学生的姓名、课程名和成绩SELECT s.sno, s.sname, c.cname, sc.grade FROM student s INNER JOIN sc ON s.sno sc.sno INNER JOIN course c ON sc.cno c.cno ORDER BY s.sno;这段SQL涉及三张表执行时是分步走的。先拿student和sc做内连接用sno相等作为匹配条件生成一个中间结果再拿这个中间结果和course连接用cno相等继续匹配。每一层都像漏斗一样过滤掉不匹配的数据。结果一共6行对应sc表里的6条选课记录。小孙在student表里存在但sc表里没有任何选课记录所以内连接结果里看不到他。C2课程虽然真实存在但除了小张没人选course表里没有重复的C2行可以和外层连接匹配所以也不会有单独一行“C2没被选”的提示。内连接就是这样的性格两边凑不齐就直接不出现。INNER JOIN可以省略INNER直接写JOIN两者等价。我建议初学阶段把INNER写全因为这样可以强化“这是在写内连接”的认知等熟悉之后再简写也不迟。3.2 LEFT JOIN 与 RIGHT JOIN主表数据一条都不能少外连接和内连接的最大区别在于它允许“一边没有匹配行”的情况也出现在结果集里。先看LEFT JOINSELECT s.sno, s.sname, sc.cno, sc.grade FROM student s LEFT JOIN sc ON s.sno sc.sno ORDER BY s.sno;LEFT JOIN的关键词是“以左表为基础”左表每一行都必须出现在结果集里。右表能匹配上就正常展示匹配不上就字段补NULL。实验数据里小孙没有选课他那一行就会出现sname是小孙但sc.cno和sc.grade全是NULL。这种结果其实就是外连接最核心的画面主表的数据一条不少从表的信息能补上都补上补不上的就留白。需要注意的是如果一开始就把所有学生都填了选课记录那LEFT JOIN的结果看起来就跟INNER JOIN没什么差别所以测试数据里空着一个小孙非常有价值。RIGHT JOIN就是把主表换到右边。SQL里可以直接把两个表调换方向SELECT sc.sno, sc.grade, s.sname FROM sc RIGHT JOIN student s ON sc.sno s.sno;右表student全保留左表sc作为补充。这个结果和前面的LEFT JOIN几乎一样因为右表本来就是所有学生。它说明一个道理RIGHT JOIN其实就是换了个方向的LEFT JOIN初学时不用把它们当成两个完全独立的语法去背只要分清“以谁为准”就够了。再看一个真正能体现RIGHT JOIN价值的例子SELECT sc.sno, sc.grade, c.cno, c.cname FROM sc RIGHT JOIN course c ON sc.cno c.cno;这次右表是course以课程为基准所有课程都会出现。C1和C3都有人选正常显示分数C2没人选那sc.sno和sc.grade就是NULL。如果想统计“哪些课完全没人选”这种RIGHT JOIN天然就把答案暴露出来了。3.3 FULL OUTER JOINMySQL里的模拟实现标准SQL里有FULL OUTER JOIN意思是左右两边的数据都全保留哪边有匹配不上就补哪边的NULL。可惜MySQL直到8.x也不直接支持这个语法所以实验环境如果是MySQL想实现“两边都全保留”的效果就得用LEFT JOIN和RIGHT JOIN各算一遍再用UNION合并。习惯的写法是这样SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s LEFT JOIN sc ON s.sno sc.sno UNION SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s RIGHT JOIN sc ON s.sno sc.sno;第一次查询以student为主把没有选课的小孙也带出来第二次查询以sc为主把可能存在的“孤儿选课记录”带出来。UNION会把两个结果集合并起来并去掉重复行。在我们的数据里因为sc有外键约束每条选课记录都能对应到student所以合并结果和LEFT JOIN基本一致。这也从侧面说明外键把数据保护得很好。用UNION拼接的时候有个硬性要求两个SELECT的列数必须一致对应列的数据类型也要兼容否则数据库直接报错。这个写法在很多面试题里也常出现建议直接把模板记住。3.4 CROSS JOIN看似没用用对场景很香交叉连接不带ON条件结果就是笛卡尔积。我用它看了一下全排列SELECT s.sno, s.sname, c.cno, c.cname FROM student s CROSS JOIN course c;student表5行course表3行结果就是5×315行。每一名学生和每一门课程都会组合一遍。实际业务中直接写CROSS JOIN的场景确实不多但它在构造测试数据、生成连续日期序列、分析“全量组合是否完整”的时候非常有用。举个例子你有一个门店表和商品表想快速生成“每个门店 × 每种商品”的组合清单用来核对哪些组合还缺库存数据CROSS JOIN就是最直接的答案。老式写法里FROM student, course其实也是交叉连接很多人不写WHERE条件结果行数直接爆炸就是因为对隐式交叉连接没有概念。做实验的时候看到结果行数等于两表行数相乘第一反应就应该是是不是有连接条件没写全。3.5 自连接同一张表也能玩出花自连接是很多人的盲区因为它要求把同一张表当成逻辑上的两张表来看。比如我想查同一个系里有哪些学生组合就可以写SELECT a.sname AS stu1, b.sname AS stu2, a.sdept FROM student a INNER JOIN student b ON a.sdept b.sdept AND a.sno b.sno ORDER BY a.sdept;这里FROM后出现了两次student表必须给它们起不同的别名a和b。查询的语义是把student表复制成逻辑上的两张独立表一张在左、一张在右然后按专业相同进行配对。加上a.sno b.sno是为了避免出现“小张小李”和“小李小张”这种重复组合也顺手排除了自己和自己的配对。自连接还有一个非常经典的场景就是查课程的先修课程。course表里的pre_cno指向的是同表里的cnoSELECT c1.cname AS 课程, c2.cname AS 先修课 FROM course c1 LEFT JOIN course c2 ON c1.pre_cno c2.cno;C2“数据结构”的先修课是C1“数据库原理”C3“操作系统”的先修课是C2“数据结构”而C1没有先修课对应位置显示NULL。这里用LEFT JOIN而不是INNER JOIN就是为了把C1也保留在结果里。自连接的本质并不复杂难的是把“同一张表”在思维里拆成两张表多写两次就习惯了。4. 连接查询的常见问题与排查心得4.1 忘了ON条件结果行数直接翻倍这是很多初学者做连接实验犯的第一个错。我曾见过有人写SELECT * FROM student, sc忘记WHERE s.sno sc.sno结果一下出来一大堆行。原因很简单隐式交叉连接把每一行学生和每一行选课记录都做了配对行数正好是两表行数的乘积。排查这类问题时有一个快速判断方法看看结果行数是否等于左表行数乘以右表行数。如果正好等于大概率就是连接条件缺失或没生效。先把ON条件补上再对左表和右表的主键取值确认数据本身没有重复基本就能解决。4.2 NULL参与连接空值永远不会等于空值SQL里NULL和任何值比较结果都是UNKNOWN。别以为NULL等于NULL在SQL的世界里它俩就是比较不上。这个特性在连接查询里会造成一个隐蔽的问题如果你拿包含NULL的字段当连接键NULL那一行永远不会匹配成功。比如在course表里C1的pre_cno是NULL如果用pre_cno去连接cnoC1这行就会从内连接结果里消失并不是因为数据错了而是逻辑使然。所以我一直强调设计表的时候连接键字段尽量设为NOT NULL外键约束也尽量建上。做连接查询之前也可以先看一眼连接键里有没有NULL免得排查半天找不到原因。4.3 连接顺序与索引查询慢的元凶多表JOIN的执行顺序数据库优化器一般会自己决定但作为写SQL的人脑子里要有一个常识尽量让结果集小的一方先参与连接也就是“小表驱动大表”。如果student表5行、sc表6行、course表3行优化器天然会倾向于拿结果集小的表当驱动表。我们改写SQL时也要有意识地遵循这个原则。光有顺序还不够连接字段必须能高效访问。如果sc.sno没有索引每拿student的一行去sc里找匹配都要全表扫描一遍复杂度直接退化成两层循环。真实项目里最常遇到的慢查询往往就是连接键缺少索引。好在我们这里sc表的主键(sno, cno)已经隐含了sno的访问路径不需要额外建索引。遇到连接慢的情况优先用EXPLAIN看执行计划里的扫描行数比瞎猜靠谱得多。4.4 ON和WHERE的语义差异别搞混外连接里ON和WHERE的过滤时机不同这是最容易把结果弄错的地方之一。LEFT JOIN执行时ON条件是在连接阶段决定右边哪些行能匹配上WHERE是在连接完成之后对中间结果再做一遍过滤。我常举一个例子想统计“所有学生以及他们是否选了C1课程”。如果写成LEFT JOIN后用WHERE sc.cno C1结果里没选C1的学生会被过滤掉主表数据就少了。正确写法是把这个条件放到ON里SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s LEFT JOIN sc ON s.sno sc.sno AND sc.cno C1;这个查询里每个学生都会出现选过C1的显示成绩没选过的课程字段是NULL。如果把同样的条件放到WHERE结果就会变成“只看选了C1的人”虽然这个结果也是有意义的但完全不是“所有学生”的统计口径。内连接里ON和WHERE的区别没那么明显但我还是建议养成好习惯关联条件放ON过滤条件放WHERE。4.5 连接查询问题速查表把我在实验里遇到过的典型问题整理成一张表方便以后排查常见症状可能原因排查思路结果行数暴涨漏写ON或WHERE触发了笛卡尔积核对连接条件确认结果行数是否等于两表乘积外连接结果比预期少过滤条件放错位置或连接方向选反确认是LEFT还是RIGHT关联条件放ON过滤条件放WHERE右表字段全部为NULL主表在从表没有匹配行这是外连接正常现象看连接键取值是否正确查询很慢连接键没索引或驱动表结果集太大用EXPLAIN看扫描行数给连接键建索引UNION报错两个SELECT列数或数据类型不一致对齐列数数据类型保持一致必要时用CAST统一这几条都是我实际实验时踩过的真实场景尤其是ON和WHERE混用那条哪怕工作过几年的人也容易搞混。对照着排查效率会提高很多。做完整轮连接查询实验我最大的感受是JOIN没有想象中那么难难的是随时清楚每张表的数据是“怎么拆出去的”以及用哪个键能“拼回来”。拆的时候知道将来怎么拼写的时候才知道以哪边为主、连哪个字段、要不要补NULL。一次实验下来犯过的错越多印象就越深刻。最后再分享一个小建议遇到连接查询结果不对别急着上网搜先做两个动作——第一把涉及的表单独查一遍确认原始数据没问题第二把连接条件、表别名、字段别名逐个对一遍。绝大多数连接查询的问题都出在这两个地方。做数据这行耐心比聪明值钱多了。