P40 40. 基础-多表查询-外连接

一句话理解

外连接在内连接的基础上，保留某一侧“配不上”的行，另一侧补 NULL。

按这 4 步做

1. 先想清楚要保住谁。要“所有员工，包括没部门的”，员工在左就用 LEFT JOIN；要“所有部门，包括没人的”，部门放右边用 RIGHT JOIN。
2. ON 里写连接条件，也可以写“只影响能不能配上”的过滤。过滤放 ON 还是 WHERE，取决于你想保留哪些左行：想保留全部员工、只给研发部的人标上部门，就把 d.name = '研发部' 放 ON，其他员工照样出现、部门列是 NULL；只要研发部员工，就把条件放 WHERE（或者干脆用内连接）。两者是不同的问题，不是对错。判断办法：把条件从 ON 挪到 WHERE，如果连本来没配上的左行也一起消失，说明这个过滤本身在筛员工，不只是筛匹配。
3. 验证时数行数：左连接保证左表每一行至少出现一次，所以结果行数不会少于左表行数；本例 department.id 是主键、一名员工最多配上一个部门，结果才恰好 4 行。若左表一行配上多个右表行，行数变多（4 行变 5 行以上）是正常放大，不是写错；变成 3 行才说明有左行被过滤掉了。
4. 统计时要分清 COUNT(*) 和 COUNT(右表列)：前者数结果行，后者只数真正匹配上的行。

小例

先准备本集练习数据：p40.sql · 练习库说明

-- 表：department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2
-- 建表与插入语句见本页练习脚本 p40.sql（独立练习库 mysqlnotes_p40，跑一次就有这两张表）

SELECT e.name AS 员工, d.name AS 部门
FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
ORDER BY e.id;

SELECT e.name AS 员工, d.name AS 部门
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.id
ORDER BY d.id;

左连接 4 行，第 4 行是“小李 / NULL”，因为他的 dept_id 为空；本例 department.id 是主键，一名员工最多配一个部门，所以没有一对多放大，行数才正好等于左表的 4 行。右连接换方向保部门，也是 4 行，最后一行“NULL / 运营部”，因为运营部还没人。想让运营部那行出现在“部门在左”的写法里，把表序换成 department LEFT JOIN employee 即可。 右连接也可以调换表序写成 LEFT JOIN，结果相同；团队里通常统一用 LEFT，方向更一致。

易错点

• LEFT JOIN 后在 WHERE 写 d.id IS NOT NULL 会滤掉 NULL 行，等价于内连接；要专门找“没部门的员工”应写 WHERE d.id IS NULL。
• COUNT(*) 数的是结果行（4），COUNT(d.id) 数的是匹配上的部门（3）；统计“多少人有部门”必须用后者。
• MySQL 没有 FULL OUTER JOIN；要两边都保留只能 LEFT UNION RIGHT，并注意去重。

30 秒速记

自测（3 题，先答再展开）

材料与来源