DQL系列写到第四篇前面几篇基本把单表查询的这些基础能力都过了一遍字段筛选、条件过滤、排序、分组、聚合函数还有去重和分页。到这一步你会发现光查一张表已经满足不了实际需求了。工作里真正要碰的数据很少会乖乖躺在一张表里等你查更多的是分散在不同的业务表里员工信息一张表、部门信息一张表、项目信息一张表互相之间靠字段关联。要拿到一份完整的报表就必须把这些表重新串起来查而这正是多表查询要做的事也是面试里最容易被追问“你有没有真正理解连接”的一个分水岭。这篇文章就顺着“DQL四”这个进度把多表连接查询一次讲透。核心内容包括为什么需要拆表、四种最常用的连接方式内连接、左外连接、右外连接、自连接、每一步SQL怎么落地、连接条件放在ON和WHERE里的区别以及我实际踩过的几个高发坑。不管是刚学完基础查询想继续进阶的新手还是写了两年SQL但一直对JOIN似懂非懂的同学这篇都应该能帮你把“多表查询”这条线彻底焊牢。1. 为什么需要多表查询1.1 数据不是越全越好拆表才是常态很多刚接触数据库的人会有一个直觉既然查询麻烦那把员工姓名、部门名称、项目名称全部塞进一张表不就行了这想法看着方便实际上一旦数据量上来就会出问题。举个最简单的例子。员工表里每个人都有一个部门字段如果直接把部门名称写进员工表那研发部有100个员工这100行里就存了100遍“研发部”。哪天研发部改名叫“技术中心”你得把员工表里的100行全部UPDATE一遍。稍微漏改一行同一个部门就出现了两个叫法后续统计报表全是脏数据。更大的问题在维护一个部门有几千个员工时改动一次的成本会非常夸张。所以数据库设计里才有一条核心原则消除冗余尽量让每份数据只存一份把它放在一个表里再通过关联字段把表之间的关系表达出来。一个员工表存员工专属信息一个部门表存部门基本信息员工表里只留一个部门编号用编号指向部门表的某一行。这样“研发部改名叫技术中心”只需要改部门表一行员工表完全不用动。这种拆分项目里的核心表往往能拆到几十张甚至上百张它们的来源多半就是这个思路。1.2 拆完之后数据怎么重新拼起来表拆开了问题也随之而来想要“员工姓名 部门名称”这样一份完整结果单查任何一张表都做不到因为员工表里只有部门编号没有部门名称部门表里有部门名称又没有员工姓名。这时就需要连接查询出场它的本质是按照关联条件把两张或多张表的行按对应的关系拼成一张“临时宽表”然后在这个临时结果上继续做筛选、排序、分组。你可以把它想成搭积木两堆积木零件放在桌面你需要根据相同颜色和编号把它们插在一起才能看出最终的整体形状。数据库里“相同的颜色和编号”就是条件通常写成a.部门编号 b.部门编号这种形式。连接查询之所以是DQL进阶的重头戏就是因为它既要懂语法又要在脑子里建立清晰的关联逻辑表之间是怎么连的连接完之后谁会多一行、谁会少一行条件写在ON和WHERE里结果为什么不同这些不搞清楚写SQL就是靠试执行结果对不对全靠运气。2. 数据库连接查询的四种基本形态2.1 内连接只留两边对得上的内连接INNER JOIN是最常见、也最容易理解的连接方式。它的规则只有一条只有两张表中都匹配得到的数据才会出现在结果里。任何一边没有对应关系的行直接丢弃。比如员工表里有个员工E没有分配部门部门表里有个后勤部一个员工都没有。用员工表和部门表做内连接查询员工和他的部门名称时员工E因为部门编号为NULL连不上任何部门结果里就不会出现后勤部因为没有员工也不会出现在结果里。两边都“对得上”的只有真正在部门表里存在、且员工确实分配了部门的组合。内连接的SQL语法有两种写法一种是老式的隐式连接把多张表写在FROM后面用WHERE写关联条件SELECT e.emp_name, d.dept_name FROM employee e, department d WHERE e.dept_id d.dept_id;另一种是标准写法用JOIN关键字和ON子句SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_id d.dept_id;第二种对初学者更友好因为连接条件和查询过滤条件分离了结构更清晰。实际项目里也建议优先用这种写法不光为了可读性后期改连接逻辑时也能少踩坑。2.2 左外连接以左边这张表为基准内连接的特点是“保守”两边都必须有数据。但工作里经常遇到这样的需求员工离职或者还没分配部门我也要把所有员工都列出来部门信息有就显示没有就留空。这种需求左外连接LEFT JOIN就是标准的解法。左外连接以“左表”为基准不管右表有没有匹配的数据左表的每一行都会出现在结果里。右表能匹配上就往对应位置填数据匹配不上就填NULL。SELECT e.emp_name, d.dept_name FROM employee e LEFT JOIN department d ON e.dept_id d.dept_id;执行这个查询返回的员工数量一定等于左表员工表的总行数。没部门的员工E会出现在结果里但他的部门名称列是NULL。这一条在实际业务里非常实用查所有员工、所有订单、所有商品然后附带关联信息都是LEFT JOIN的典型场景。2.3 右外连接和左外连接的互换右外连接RIGHT JOIN的逻辑和左外连接完全对称以右表为基准右表所有行都保留左表匹配不上的用NULL填充。SELECT e.emp_name, d.dept_name FROM employee e RIGHT JOIN department d ON e.dept_id d.dept_id;这个查询的结果里后勤部虽然没有任何员工但它依然会出现在结果中只是员工姓名列是NULL。你可能会觉得奇怪既然两个方向都能写到底用哪个我的习惯是能用LEFT JOIN表达的就优先用LEFT JOIN统一从左向右读语义更好理解。需要以某个表为主表而它又正好在右边时把FROM后面的表顺序颠倒一下用LEFT JOIN替代RIGHT JOIN效果完全一致。右外连接语法本身没有错只是用多了之后左右语义来回切换容易糊涂统一成一个方向能省不少脑力。2.4 自连接一张表自己跟自己连自连接是看起来最抽象、用起来最巧妙的一种连接方式。它本质上也是一次连接查询只不过连接的两边是同一张表。最常见的场景是层级关系员工表里有一个“直属上级编号”字段指向的其实是同一个员工表里的另一行。比如员工B的上级是员工A员工C的上级也是员工A。要查“每个员工的上级叫什么名字”就需要把员工表既当作“员工表”来取员工自己的名字又当作“上级表”来获取上级的名字。这在物理上是一张表在逻辑上却扮演了两个角色。因为计算机无法区分两份数据所以必须给这张表起两个不同的别名。SELECT e.emp_name AS 员工, m.emp_name AS 上级 FROM employee e LEFT JOIN employee m ON e.manager_id m.emp_id;这里e代表“员工本人”这张视图m代表“上级信息”这张视图。把同一个表用不同别名看成一个或多个虚拟表很多一对一的层级关系就都能靠这个方式解出来。组织架构、商品分类的父子关系、评论的楼中楼回复全部是自连接的典型应用。3. 实操全演示从建表到跑通每条SQL3.1 建表和测试数据准备光看不练很容易原地踏步我们直接把表建出来用真实数据把每种连接跑一遍。需要两张表部门表department和员工表employee。员工表里除了部门编号再加一个manager_id表示直属上级用来演示自连接。建表语句如下CREATE TABLE department ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) NOT NULL ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, dept_id INT, manager_id INT );插入演示数据我故意设计了几条边界情况INSERT INTO department (dept_id, dept_name) VALUES (1, 研发部), (2, 市场部), (3, 人事部), (4, 后勤部); INSERT INTO employee (emp_id, emp_name, dept_id, manager_id) VALUES (1, 员工A, 1, NULL), (2, 员工B, 1, 1), (3, 员工C, 2, 1), (4, 员工D, 3, 2), (5, 员工E, NULL, 3);这份数据里有几个刻意设计的地方员工E没有部门后勤部没有任何员工员工A没有上级员工D的上级是员工B。这四个边界情况正好能覆盖四种连接的一部分差异后面每一步的结果都能直接对比。3.2 内连接实操和两种写法对比先用标准内连接查询所有员工的名字和所在部门名称SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_id d.dept_id;结果如下emp_namedept_name员工A研发部员工B研发部员工C市场部员工D人事部注意结果里没有员工E因为他的dept_id是NULL在部门表里找不到对应的行也没有后勤部因为后勤部没有任何员工。这就是内连接的“严格匹配”只有两边都对上才留下。再用老式写法跑一遍SELECT e.emp_name, d.dept_name FROM employee e, department d WHERE e.dept_id d.dept_id;结果完全一样但从可读性上讲我强烈推荐你今后用INNER JOIN的写法。老式写法在表很多时WHERE里会混入一堆连接条件和其他过滤条件后期维护非常痛苦。而且一旦漏写WHERE条件会直接产生笛卡尔积后面专门讲这个坑。3.3 外连接实操观察NULL行改成左外连接SELECT e.emp_name, d.dept_name FROM employee e LEFT JOIN department d ON e.dept_id d.dept_id;结果是5行emp_namedept_name员工A研发部员工B研发部员工C市场部员工D人事部员工ENULL和刚才内连接的结果对比员工E被保留了部门名称列上是NULL。这正是左外连接的核心特点左表employee有多少行结果至少有多少行。再换成右外连接SELECT e.emp_name, d.dept_name FROM employee e RIGHT JOIN department d ON e.dept_id d.dept_id;结果是5行emp_namedept_name员工A研发部员工B研发部员工C市场部员工D人事部NULL后勤部这里保留的是右表department的全部行后勤部因为没员工员工姓名列就是NULL。如果你希望结果里优先完整展示部门而不是完整展示员工右外连接就能直接满足。实战中我大多会把表顺序调整成LEFT JOIN来写但方向弄懂之后两种怎么用都行。3.4 自连接案例查询员工的上级最后一个实操是自连接需求是显示“员工姓名 直属上级姓名”上级也在这张员工表里SELECT e.emp_name AS 员工, m.emp_name AS 上级 FROM employee e LEFT JOIN employee m ON e.manager_id m.emp_id;结果员工上级员工ANULL员工B员工A员工C员工A员工D员工B员工E员工C这里我用了LEFT JOIN而不是INNER JOIN原因很实际员工A没有上级如果用内连接员工A这一行会因为manager_id是NULL而直接被丢弃但业务上我们依然希望看到“员工A存在但没有上级”所以用LEFT JOIN更合理。判断用内连接还是外连接最好在写SQL之前先想清楚主表里的行即使关联不上要不要保留在结果里。4. 连接类型怎么选ON和WHERE怎么放4.1 按业务含义选连接的速查表很多人写完JOIN语法后总在“到底用LEFT JOIN还是INNER JOIN”之间犹豫。我平时给自己定的判断标准很简单看主表行是否必须全部出现。需求描述推荐连接只保留两边都匹配到的数据INNER JOIN左边那张表的每行都必须出现匹配不上就留空LEFT JOIN右边那张表的每行都必须出现匹配不上就留空RIGHT JOIN在同一张表内部查上下级、父子关系自连接主表所有行必须出现但不想产生重复附属表信息可有可无LEFT JOIN 去重检查实际项目里最常见的组合是主表订单表、员工表、商品表加上若干附属表用户表、部门表、分类表用LEFT JOIN把附属表的信息补进来。因为这些主表在业务上都是“核心实体”每一行都代表一条真实业务记录不能因为某个附属信息缺失就整体丢掉。什么时候用INNER JOIN多发生在过滤场景比如“只查哪些用户下过单”“哪些员工确实分配了部门”两边都必须存在才符合业务含义。4.2 LEFT JOIN下ON与WHERE的天壤之别这个点我必须单独拿出来讲因为实际面试和工作中它是区分“真懂连接”和“背语法”的典型问题。同一个LEFT JOIN语句条件写在ON里和WHERE里结果可能完全不同。举个例子想查所有员工及其部门名称但只关心“研发部”的员工写法一条件放在ON里SELECT e.emp_name, d.dept_name FROM employee e LEFT JOIN department d ON e.dept_id d.dept_id AND d.dept_name 研发部;这个查询返回5行员工A和员工B是研发部所以有部门名称员工C、员工D、员工E因为不满足“研发部”这个条件部门名称全部是NULL。也就是说ON里的条件影响的是匹配过程它决定了右表哪些行能参与连接匹配但不影响左表行的保留。写法二条件放在WHERE里SELECT e.emp_name, d.dept_name FROM employee e LEFT JOIN department d ON e.dept_id d.dept_id WHERE d.dept_name 研发部;这个查询只返回2行只有员工A和员工B。因为LEFT JOIN执行完后会先生成5行中间结果然后WHERE在最后做一次整体过滤把部门名称不是研发部的行全部删掉。本质是“先连接、再过滤”相当于把LEFT JOIN变成了INNER JOIN的效果。两种写法的区别一句话概括ON里的额外条件只影响“匹配谁”不影响“保留谁”WHERE里的条件是最终过滤直接影响结果行数。这在写“主表全保留、附属表缩小范围”的查询时特别容易踩雷很多人明明想保留所有员工只是想过滤掉某些非研发部的部门信息却把研发部条件误放到了WHERE里最终结果少了一大截。4.3 多表连接与顺序问题两张表的JOIN理解了三张表其实就是重复同一个动作。比如要查员工、部门、还有员工参与的额外项目最简单的做法就是从左到右依次LEFT JOINSELECT e.emp_name, d.dept_name, p.project_name FROM employee e LEFT JOIN department d ON e.dept_id d.dept_id LEFT JOIN emp_project ep ON e.emp_id ep.emp_id LEFT JOIN project p ON ep.project_id p.project_id;注意连接顺序一般不影响最终结果MySQL的优化器会自己调整表连接的顺序。但我在项目里还是会按业务关系书写先写主表再写直接关联的表最后写间接关联的表。这样别人看你的SQL时能顺着从左到右读懂逻辑这个习惯比理论上“顺序无所谓”重要得多。多表连接还有一个容易忽略的点一张表关联两张独立的子表时可能会产生数据重复。比如员工同时参与3个项目LEFT JOIN项目表后员工这一行会变成3行。出现重复行不一定代表SQL写错但一定要心里有数知道重复是业务关系导致的结果后续做统计时别直接SUM否则数字会被放大。5. 常见报错与查询陷阱梳理5.1 列名不唯一ERROR 1052多表连接时最常遇到的第一类报错就是列名歧义。员工表和部门表里都有dept_id如果你直接写SELECT dept_id FROM employee e INNER JOIN department d ON e.dept_id d.dept_id;MySQL会报错ERROR 1052 (23000): Column dept_id in field list is ambiguous意思是数据库无法判断你想要的dept_id到底来自哪张表。解决方式很直接给每一张表起别名字段前都带上表别名前缀。养成这个习惯之后给人看SQL、排查字段来源都要快很多。5.2 漏了连接条件笛卡尔积这是每个新手必踩的坑。写了多表连接却忘了写ON条件或者老式写法里忘了WHERE条件数据库会把两张表的每一行都做一次组合。员工表5行、部门表4行不写连接条件的结果就是5×420行里面全是乱配的数据。这种结果表面上看起来“数据变多了”实际上完全没意义如果后续还做统计数字直接崩。排查方法也很简单写完多表查询后先看一眼结果行数是否合理。如果你心里清楚员工只有5个结果却出现了20行那八成就是连接条件漏了或者在连接过程中因为一对多关系产生了重复。把ON条件补上问题立刻解决。5.3 连接方向选错主表行数丢了还有一种症状不那么明显的错误查询结果没有报错行数却比预期少。比如查“所有员工及其部门名称”结果发现没分配部门的员工不见了多半就是把LEFT JOIN写成了INNER JOIN。这种错误的可怕之处在于数据少了几行程序不报错人眼一眼扫过去也未必注意得到。所以我在执行完多表查询后习惯先对比一次主表行数这个查询返回的结果是不是等于主表的总行数。如果不等于先问自己一句主表里有没有关联不上、但业务上必须保留的行有的话连接类型就要考虑外连接。5.4 性能提醒连接列最好有索引多表查询的另一个隐藏成本在性能。连接的本质是拿A表的每一行去B表里找匹配的行。如果B表的连接字段上没有索引数据库就要全表扫描一整遍表一多、数据一大慢查询就来了。最典型的优化方法就是给连接条件下经常用来关联的字段加索引也就是给employee.dept_id、employee.manager_id这类字段创建普通索引。加了索引之后数据库定位匹配行会快非常多。这种性能问题在小数据量时完全感觉不到但一旦表里几十万、上百万条记录连接查询慢几秒甚至几十秒都非常正常。我现在的习惯是新写一条多表查询先EXPLAIN看一眼执行计划确认连接字段有没有用上索引再决定要不要调整。这比在慢SQL出现以后到处捞日志要省事得多。回到开头那个问题为什么多表查询是DQL的分水岭因为单表查询考察的是“你会不会用语法”多表查询考察的是“你的数据模型到底理不理解”。写法和规则其实就那么几条内连接留交集外连接保主表自连接解决层级ON管匹配、WHERE管过滤。把这几条理顺再复杂的报表需求拆开看也无非是在这些基础之上做组合而已。我个人实际写过一阵子之后的感受是比起死记连接类型更重要的是在写SQL前先想清楚业务到底想保留哪一边的数据。这个方向想对了SQL写出来自然就是对的。