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

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 = '张三';

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;

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 的列填 NULLSELECTs.*,sc.scoreFROMstudent sLEFTJOINscore scONsc.student_id=s.idANDsc.subject='数学';-- 所有学生都在；没考数学的学生 score 是 NULL-- WHERE 过滤：先 LEFT JOIN，结果再 WHERE 过滤SELECTs.*,sc.scoreFROMstudent sLEFTJOINscore scONsc.student_id=s.idWHEREsc.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;

七、JOIN 性能优化

7.1 驱动表选择

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

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

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

判断"小表"的方法：

-- 看两表行数SELECTCOUNT(*)FROMA;SELECTCOUNT(*)FROMB;

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

-- 即使 A 行数多，如果 WHERE 过滤后只剩 10 行，A 仍是小驱动表EXPLAINSELECT*FROMAJOINBONA.id=B.a_idWHEREA.status=1;

7.2 必须为连接字段建索引

-- score.student_id 上必须有索引SELECTs.*,sc.scoreFROMstudent sJOINscore scONsc.student_id=s.id;

EXPLAINSELECT*FROMstudent sJOINscore scONsc.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 有索引SELECTs.*,sc.*FROMstudentASs-- 驱动表（小）JOINscoreASscONsc.student_id=s.id;-- 被驱动表（大，靠索引定位）

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

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

-- ❌ 大表驱动 + 被驱动表无索引SELECT*FROMscore scJOINstudent sONs.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 }, ...]

九、最佳实践

十、小结

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