Node系列 · 数据库：联表查询

单表查询覆盖 80% 业务场景，剩下 20% 是"数据分散在多张表"——必须靠 JOIN 合并。本章讲清楚三种 JOIN 的行为差异、何时选哪个、JOIN 性能优化。

一、为什么需要 JOIN

关系型数据库强调单表职责单一——用户表存用户、订单表存订单、文章表存文章。要查"张三的所有订单"就需要联表：

student (id, name, class_id)        ← 学生
score   (id, student_id, subject, score)  ← 成绩

"查张三的所有数学成绩"需要把两张表按 student_id 拼起来。

二、三种 JOIN

2.1 INNER JOIN（内连接）

只保留两表都有匹配的行——交集。

SELECT s.id, s.name, sc.subject, sc.score
FROM student AS s
INNER JOIN score AS sc ON sc.student_id = s.id
WHERE s.name = '张三';

维度	INNER JOIN
匹配规则	两边都满足 ON 条件
结果集	交集（A ∩ B）
NULL 行	不保留
性能	通常最快（只扫匹配的行）

2.2 LEFT JOIN（左外连接）

保留左表全部行，右表没匹配就填 NULL——左表全集 + 右表匹配。

-- 查每个学生 + 他们的成绩（没考的学生也列出，成绩为 NULL）
SELECT s.id, s.name, sc.subject, sc.score
FROM student AS s
LEFT JOIN score AS sc ON sc.student_id = s.id;

维度	LEFT JOIN
匹配规则	左表全部保留
右表无匹配	字段填 NULL
结果集	左表全集 + 右表匹配
典型场景	"找没下过单的用户"、"找没评论的文章"

2.3 RIGHT JOIN（右外连接）

与 LEFT JOIN 对称——保留右表全部行，左表没匹配就填 NULL。

SELECT s.id, s.name, sc.subject, sc.score
FROM student AS s
RIGHT JOIN score AS sc ON sc.student_id = s.id;

WARNING

RIGHT JOIN 几乎不用。因为 A RIGHT JOIN B 等价于 B LEFT JOIN A——直接交换表顺序用 LEFT JOIN 更易读。生产代码里看到 RIGHT JOIN 通常会被同事吐槽。

2.4 FULL OUTER JOIN（MySQL 不直接支持）

MySQL 没有 FULL OUTER JOIN 关键字，但可以用 UNION 模拟 LEFT + RIGHT：

SELECT s.*, sc.* FROM student s LEFT JOIN score sc ON sc.student_id = s.id
UNION
SELECT s.*, sc.* FROM student s RIGHT JOIN score sc ON sc.student_id = s.id;

三、JOIN 结果集可视化

四、ON 与 WHERE 的关键差异

ON 和 `WHERE 都能过滤行，但作用时机不同：

-- ON 过滤：保留左表全部行，右表不满足 ON 的列填 NULL
SELECT s.*, sc.score
FROM student s
LEFT JOIN score sc ON sc.student_id = s.id AND sc.subject = '数学';
-- 所有学生都在；没考数学的学生 score 是 NULL

-- WHERE 过滤：先 LEFT JOIN，结果再 WHERE 过滤
SELECT s.*, sc.score
FROM student s
LEFT JOIN score sc ON sc.student_id = s.id
WHERE sc.subject = '数学';
-- 只剩考了数学的学生；LEFT JOIN 退化成 INNER JOIN

TIP

LEFT JOIN + WHERE = INNER JOIN。这是一个常见的反直觉点。LEFT JOIN 的"保留左表全部行"承诺，只在 ON 阶段生效；进入 WHERE 阶段后，所有行（包括 NULL）都参与过滤。

五、多表 JOIN

可以连续 JOIN 多张表：

SELECT
  s.id, s.name,
  c.name AS class_name,
  sc.subject, sc.score
FROM student s
JOIN class  c  ON c.id = s.class_id
LEFT JOIN score sc ON sc.student_id = s.id
WHERE s.sex = b'1';

每加一个 JOIN 关联一张表。表越多性能越差——超过 5 张表要审视设计。

六、自连接（Self Join）

表自己连自己，用于"上下级关系"、"相邻行"等场景：

employee
┌────┬───────────┬──────────┐
│ id │ name      │ manager_id │
├────┼───────────┼──────────┤
│  1 │ 张总      │ NULL     │
│  2 │ 王经理    │ 1        │
│  3 │ 李员工    │ 2        │
└────┴───────────┴──────────┘

查"每个员工的直属上级姓名"：

SELECT e.name AS employee, m.name AS manager
FROM employee AS e
LEFT JOIN employee AS m ON m.id = e.manager_id;

employee	manager
张总	NULL
王经理	张总
李员工	王经理

七、JOIN 性能优化

7.1 驱动表选择

MySQL JOIN 是嵌套循环：每扫一行驱动表，就在被驱动表上做一次查询。

-- A 是驱动表（外层循环）
-- B 是被驱动表（内层循环，靠索引定位）
SELECT * FROM A JOIN B ON A.id = B.a_id;

优化原则：小表驱动大表，被驱动表的连接字段必须有索引。

判断"小表"的方法：

-- 看两表行数
SELECT COUNT(*) FROM A;
SELECT COUNT(*) FROM B;

但更关键的是过滤后的行数：

-- 即使 A 行数多，如果 WHERE 过滤后只剩 10 行，A 仍是小驱动表
EXPLAIN SELECT * FROM A JOIN B ON A.id = B.a_id WHERE A.status = 1;

7.2 必须为连接字段建索引

-- score.student_id 上必须有索引
SELECT s.*, sc.score
FROM student s
JOIN score sc ON sc.student_id = s.id;

EXPLAIN SELECT * FROM student s JOIN score sc ON sc.student_id = s.id;

WARNING

看 EXPLAIN 输出里的 type 列：

• system / const：最优
• eq_ref：被驱动表主键或唯一索引 JOIN，最佳 JOIN 性能
• ref：被驱动表普通索引 JOIN，OK
• index：全索引扫描，慢
• ALL：全表扫描，必须优化

7.3 小表驱动大表 + 索引 = 高效 JOIN

-- score 表有 100 万行，student 表有 1000 行
-- student.id 是主键，score.student_id 有索引
SELECT s.*, sc.*
FROM student AS s           -- 驱动表（小）
JOIN score AS sc ON sc.student_id = s.id;  -- 被驱动表（大，靠索引定位）

执行计划：扫 1000 行 student，每行去 score 走索引查 → 1000 次索引查询。

7.4 反例：大表驱动 + 索引失效

-- ❌ 大表驱动 + 被驱动表无索引
SELECT * FROM score sc JOIN student s ON s.class_id = sc.class_id;
-- score 100 万行扫一遍；student.class_id 无索引 → 全表扫描 100 万次
-- 总操作：100 万 × 100 万 = 1 万亿次比较

八、Node 端 JOIN 查询

const mysql = require('mysql2/promise');

const pool = mysql.createPool({ /* config */ });

const [rows] = await pool.execute(
  `SELECT s.id, s.name, sc.subject, sc.score
   FROM student s
   LEFT JOIN score sc ON sc.student_id = s.id
   WHERE s.class_id = ?
   ORDER BY s.id
   LIMIT ?`,
  [3, 20]
);

console.log(rows);
// [{ id: 1, name: '张三', subject: '数学', score: 95 }, ...]

九、最佳实践

场景	推荐
两表关联	INNER JOIN（要交集）或 LEFT JOIN（要左表全集）
不用 RIGHT JOIN	改写为 LEFT JOIN + 表交换
表超过 5 张	审视设计，或拆查询后应用层合并
性能瓶颈	EXPLAIN 分析执行计划
必做	被驱动表连接字段必须有索引
驱动表选择	小表驱动大表（过滤后行数小）
找不到记录	LEFT JOIN + 右表 IS NULL 找"没匹配"

十、小结

• 三种 JOIN：INNER（交集）/ LEFT（左表全集 + 右表匹配）/ RIGHT（右表全集 + 左表匹配）
• LEFT JOIN + WHERE = INNER JOIN——LEFT JOIN 的"保留全集"只在 ON 阶段有效
• ON 过滤行匹配，WHERE 过滤最终结果
• 多表 JOIN 不超过 5 张；自连接用表的别名实现"自己连自己"
• 性能关键：小表驱动大表 + 被驱动表连接字段有索引
• 用 EXPLAIN 验证执行计划，关注 type 列（避免 ALL）