适用读者：能把多表 JOIN 写出来、但被"ON 和 WHERE 差在哪"问住的人。
前置：S11-J06、B03、S03（数据结构：连接算法需要）。
覆盖旧文：4523（第 4 章运算符）、4527/4528（第 6 章多表查询 + 练习）、4531/4532（第 8 章聚合 + 练习）、4533/4534（第 9 章子查询 + 练习）、4530（练习片段）→ 10 篇 / 约 5 万字符。
扩展点：E-S11-022（ON vs WHERE：外连接的语义分界）、E-S11-023（子查询三形态与"能改 JOIN 就改 JOIN"的判据）、E-S11-024（聚合与窗口函数的选型）。
上一篇：S11-J06 · SQL 基础与函数速通：从建表到聚合
下一篇：补编第 3 篇 · 约束、变量、游标与触发器（待写）。

---

1. 一句话结论

多表查询的全部难点集中在两处：外连接里 ON 与 WHERE 的位置（决定 NULL 行留不留），以及子查询能否被物化（决定性能）。语法会背不等于答对，因为这两处都不报错。

出处：A-4527/A-4528（第 6 章）、A-4533/A-4534（第 9 章）、A-4531（第 8 章）。教材只给练习与零散示例，答案由本系列补写（标记为新增内容 E）。

---

2. 连接语义与 ON/WHERE 的分界（E-S11-022）

-- A: 内连接（两表都匹配才出行）
SELECT e.name, d.dept_name FROM emp e JOIN dept d ON e.dept_id = d.id;

-- B: 左外连接（左表全保留，右表无匹配则右列 NULL）
SELECT e.name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

关键差别（必答）：

-- ① 过滤条件放 ON：在外连接中"不影响保留左表"，只影响右表匹配
LEFT JOIN dept d ON e.dept_id = d.id AND d.status = 'ACTIVE'   -- 未匹配的部门仍保留 e 行
-- ② 过滤条件放 WHERE：会把右列为 NULL 的行整体过滤掉 → 退化为内连接
LEFT JOIN dept d ON e.dept_id = d.id WHERE d.status = 'ACTIVE' -- 丢掉所有无部门员工

判据：外连接里"右表的过滤条件"必须放 ON；"左表的过滤条件"放 WHERE 或 ON 都可。 这是外连接唯一的语义分界线。

连接类型	结果行	典型用途
INNER JOIN	双匹配	常规关联查询
LEFT JOIN	左全 + 右匹配	查"没有"的数据（右列 IS NULL）
RIGHT JOIN	右全	少用，可用 LEFT 改写
FULL JOIN	MySQL 不支持，用 LEFT UNION RIGHT	双向补全
CROSS JOIN	笛卡尔积	生成序列/日期维度

MySQL 的执行算法：Nested Loop（默认）、Block Nested Loop（5.6~8.0.17，有大缓冲但被弃用）、Hash Join（8.0.18+，等值连接自动启用）。EXPLAIN FORMAT=TREE 可直接看到 Inner hash join。

---

3. 子查询三形态（E-S11-023）

形态	例子	特征	性能建议
标量子查询	WHERE salary > (SELECT AVG(salary) FROM emp)	返回单值，可缓存	通常可接受
IN / EXISTS 子查询	WHERE dept_id IN (SELECT id FROM dept)	半连接语义	优先 EXISTS（尤其含 NULL 风险时）
派生表（FROM 子查询）	FROM (SELECT ...) t JOIN ...	需物化或合并	8.0 可用 LATERAL（8.0.14+）

"能改 JOIN 就改 JOIN"的判据：

① 8.0 优化器已能把多数 IN/EXISTS 改写为半连接（semi-join），性能接近
② 但相关子查询（correlated）在旧版本会逐行执行 → 必须改 JOIN 或 LATERAL
③ NOT IN 含 NULL 会返回空集 → 必须改 NOT EXISTS（语义正确性问题，不是性能问题）

教材练习补写示例（A-4534 原为题干，答案新增）：

-- 题：查每个部门薪资高于本部门平均薪资的员工
SELECT e.name, e.dept_id, e.salary
FROM emp e
JOIN (SELECT dept_id, AVG(salary) avg_s FROM emp GROUP BY dept_id) d
  ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_s;                       -- ✅ 派生表只算一次
-- 对照：相关子查询（每行重算）
WHERE e.salary > (SELECT AVG(salary) FROM emp x WHERE x.dept_id = e.dept_id);

---

4. 聚合：执行顺序与三个坑

SELECT dept_id, COUNT(*) AS c, AVG(salary) AS avg_s
FROM emp
WHERE status = 'ACTIVE'
GROUP BY dept_id
HAVING COUNT(*) >= 3
ORDER BY c DESC;

逻辑执行顺序（必背）：

FROM → ON → JOIN → WHERE → GROUP BY → 聚合函数 → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

坑	说明
WHERE 里用聚合	报错，聚合只能在 HAVING/SELECT
COUNT(*) vs COUNT(col)	后者跳过 NULL；COUNT(DISTINCT col) 再去重
SUM 返回 NULL	空集合上 SUM 为 NULL，不是 0 → 外层要 IFNULL(SUM(x),0)
GROUP BY 隐式排序	8.0 起不再隐式排序（5.7 会），旧代码依赖排序会出问题
ONLY_FULL_GROUP_BY	8.0 默认开，非聚合列必须出现在 GROUP BY

4.1 聚合 vs 窗口函数（E-S11-024）

-- 需求：每个员工 + 本部门平均（保留明细行）→ 必须用窗口函数
SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM emp;

-- 需求：只要每组一行 → 用 GROUP BY
SELECT dept_id, AVG(salary) FROM emp GROUP BY dept_id;

选择	判据
GROUP BY	结果行数变少（每组一行）
窗口函数	结果保留明细行，附加聚合值
二者混用	先窗口再聚合，注意顺序与 PARTITION BY 的一致性

版本边界：窗口函数 8.0 才有；5.7 需用自连接或变量模拟（旧教程里那些"用变量算排名"的示例属 5.7 遗产）。

---

5. 教材练习补写（标记为新增内容）

来源	原状态	补写内容
A-4528（第 6 章练习）	只有题干	左连接查"无部门员工"：LEFT JOIN ... WHERE d.id IS NULL
A-4532（第 8 章练习）	只有题干	分组后过滤用 HAVING；空集 SUM 需 IFNULL
A-4534（第 9 章练习）	只有题干	见 §3 派生表改写
A-4530（显示系统时间）	片段	SELECT NOW(), CURDATE(), UTC_TIMESTAMP();
A-4521（SELECT * FROM emp\G）	片段	\G 竖排输出，仅命令行可用，脚本禁用

---

6. 反例：五种"语法对、结果错"

写法	结果
LEFT JOIN ... WHERE d.status='ACTIVE'	退化为内连接
NOT IN (SELECT dept_id FROM emp)	子查询含 NULL → 空集
SELECT dept, name FROM emp GROUP BY dept	8.0 报 ONLY_FULL_GROUP_BY 错
SUM(x) 直接比较	空集为 NULL，比较恒 false
依赖 5.7 的 GROUP BY 隐式排序	8.0 结果顺序变化

---

7. 自测

1. 外连接里 ON 与 WHERE 放过滤条件的差别？举一例。
2. MySQL 8.0.18+ 的等值连接默认用什么算法？怎么看？
3. 子查询三形态？"能改 JOIN 就改 JOIN"的两个例外？
4. SQL 逻辑执行顺序？
5. 聚合与窗口函数的选型判据？

答案

1. ON 里的右表条件不丢左表行；WHERE 里的会丢，退化为内连接。
2. Hash Join；EXPLAIN FORMAT=TREE 看 Inner hash join。
3. 标量 / IN-EXISTS / 派生表；相关子查询、NOT IN 含 NULL。
4. FROM→ON→JOIN→WHERE→GROUP BY→聚合→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT。
5. 行数变少用 GROUP BY；保留明细用窗口函数。

---

8. 下一篇

补编第 3 篇 · 约束、变量、游标与触发器 —— 六类约束、ON DELETE 三种动作、会话/全局变量、游标的 DECLARE...HANDLER、触发器与存储函数的可维护性边界。

---

本篇属于系列《S11 · 数据库与 SQL》。旧文来源：A-4523–4534（教材章 + 练习）；扩展点 E-S11-022/023/024。教材练习原为题干，答案由本系列补写（新增内容）。