多表关联查询

🎯 引言

前面几篇我们都在一张表里查询数据，但真实项目里数据会分布在多张表中。学完这篇文章，你能说清楚为什么要把数据拆到多张表，能用 INNER JOIN 和 LEFT JOIN 把两张表的数据拼在一起查询，还能说清楚这两种 JOIN 的区别，写出带关联查询的统计 SQL。

🧱 为什么要把数据拆到多张表

假设我们要做一个博客，需要保存用户信息和用户发表的文章。你可能会想：全部塞进一张表不就行了？

发现问题了吗？张三发表了两篇文章，他的姓名和年龄就被重复存储了两次。这就像寄快递时，每寄一个包裹都要把收件人的姓名、电话、地址完整抄一遍，明明是同一个人，却要重复抄写。

这种设计会带来麻烦：

• 浪费空间：同一份用户信息存了很多份。
• 修改困难：用户改名时，要更新几十上百行，漏掉一行数据就不一致了。
• 无法表达没有文章的用户：新用户还没发文，就没法存进这张表。

正确的做法是拆表：用户信息放 user 表，文章信息放 article 表，文章里只记录一个 user_id 表示作者是谁。查询时再用 JOIN 把两张表拼起来。

🛠 准备两张表演示数据

我们先建一张 user 用户表，并插入几个用户：

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user (username, age, city) VALUES
('张三', 25, '北京'),
('李四', 30, '上海'),
('王五', 28, '广州');

插入完成后，user 表的数据如下：

created_at 我们没有手动插入，MySQL 会自动填充为插入时刻，这里就不展示了。

再建一张 article 文章表，用 user_id 记录每篇文章的作者：

CREATE TABLE article (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    user_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO article (title, user_id) VALUES
('学习 SQL 基础', 1),
('学习 JOIN 查询', 1),
('前端入门指南', 2),
('一篇无主的文章', 99);

插入完成后，article 表的数据如下：

🛠 INNER JOIN：只保留两边都匹配的数据

现在想查出「每篇文章的标题和作者用户名」，就需要把两张表拼起来。INNER JOIN（内连接） 的作用是根据条件把两张表匹配的行拼成一行：

SELECT article.title, user.username
FROM article
INNER JOIN user ON article.user_id = user.id;

执行结果如下：

ON article.user_id = user.id 是连接条件，意思是：把 article 表的 user_id 和 user 表的 id 相等的行拼在一起。可以把它理解成按工牌号核对名单，两边能对上号的才会出现在结果里。

注意两个细节：

• 王五没有文章，user 表里没有能匹配他的 article 行，所以他没有出现在结果中。
• 「一篇无主的文章」的 user_id 是 99，在 user 表中找不到对应用户，同样被丢弃了。

INNER JOIN 只保留两边都能匹配上的数据，匹配不上的行会被丢弃。

⚡ LEFT JOIN：左边没匹配也保留

如果我们的需求是「查出所有文章，有作者的显示作者名」，INNER JOIN 会把无主文章丢掉，这就不合适了。这时要用 LEFT JOIN（左连接）：

SELECT article.title, user.username
FROM article
LEFT JOIN user ON article.user_id = user.id;

执行结果如下：

区别很明显：LEFT JOIN 会把左表的所有行都保留，左表指 FROM 后面紧跟的那张表。左表的行在右表找不到匹配时，右侧的字段会显示为 NULL。

反过来，如果左表写 user，就能查出所有用户，包括没发文章的王五：

SELECT user.username, article.title
FROM user
LEFT JOIN article ON article.user_id = user.id;

执行结果如下：

💡 用表别名简化写法

JOIN 查询里反复写完整表名很啰嗦，SQL 支持给表起别名。习惯做法是取表名首字母：

SELECT a.title, u.username
FROM article a
LEFT JOIN user u ON a.user_id = u.id;

FROM article a 表示给 article 表起别名 a，后面就可以用 a.title 代替 article.title。别名只在当前这条 SQL 内有效，实际项目中多表关联时几乎都会用别名。

这条 SQL 和上面那条 LEFT JOIN 完全等价，执行结果也相同：

🛠 JOIN 配合 GROUP BY 做统计

JOIN 还可以和前面学过的 GROUP BY 组合使用。比如统计每个用户发表了多少篇文章：

SELECT u.username, COUNT(a.id) AS article_count
FROM user u
LEFT JOIN article a ON a.user_id = u.id
GROUP BY u.id, u.username;

执行结果如下：

这里有两个关键点：

• 用 LEFT JOIN 而不是 INNER JOIN，王五没有文章，用 INNER JOIN 他就会从统计结果里消失。
• 用 COUNT(a.id) 而不是 COUNT(*)。COUNT(字段) 只统计该字段不为 NULL 的行，王五那行的 a.id 是 NULL，所以计数为 0，正好符合预期。

🧰 外键约束：给关联加一道保险

前面那篇 user_id 为 99 的无主文章，本质上是数据不一致：文章记录了一个不存在的作者。MySQL 提供了外键约束（FOREIGN KEY） 来防止这种情况：

CREATE TABLE article (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    user_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

加了外键约束后，article.user_id 的值必须在 user.id 里真实存在，插入 user_id 为 99 的文章会直接报错；想删除一个还有文章的用户，也会被数据库拦下。

🧾 小节总结

• 把数据拆到多张表可以避免重复存储，让修改更方便，查询时用 JOIN 再拼起来。
• INNER JOIN 只保留两张表都匹配上的行，匹配不上的行会被丢弃。
• LEFT JOIN 保留左表的所有行，右表匹配不上时对应字段显示为 NULL。
• 可以用 FROM article a 给表起别名，简化多表查询的写法。
• JOIN 配合 GROUP BY 能做跨表统计，统计数量时用 COUNT(字段) 可以让无关联数据计为 0。
• 外键约束 FOREIGN KEY 能保证关联数据的一致性，学习阶段了解即可。

❓ 知识问答

Q1：INNER JOIN 和 LEFT JOIN 怎么选？

看需求要不要保留没匹配上的数据。只要两边都有的数据用 INNER JOIN，比如「有作者的文章列表」；要保留左表全部数据用 LEFT JOIN，比如「所有用户及其文章数」。

Q2：RIGHT JOIN 是什么？

和 LEFT JOIN 效果对称，保留右表的所有行。它完全可以靠调换表的位置用 LEFT JOIN 实现，实际开发中 LEFT JOIN 更常用。

Q3：JOIN 会改变原表的数据吗？

不会。JOIN 只是在查询时把多张表临时拼接成结果集，原表数据不受影响，和 SELECT 一样是只读操作。

Q4：三张表能一起 JOIN 吗？

可以，连续写多个 JOIN 即可，例如 FROM a JOIN b ON ... JOIN c ON ...。思路和两表关联一样，只是拼接次数更多。

Q5：ON 和 WHERE 都能写条件，有什么区别？

ON 是连接条件，决定两张表怎么匹配；WHERE 是对 JOIN 结果再过滤。在 LEFT JOIN 中把条件从 ON 挪到 WHERE，结果可能完全不同，初学时建议连接条件固定写在 ON 里。

🧪 小练习

基于本文的 user 表和 article 表完成下面两个练习：

练习 1：查询所有文章的标题和作者用户名，要求保留没有作者的文章（author 显示为 NULL），并使用表别名。

-- 请在这里编写 SQL

练习 2：统计每个城市的用户发表的文章总数，结果按文章总数从多到少排序。提示：JOIN 之后按 u.city 分组。

-- 请在这里编写 SQL

🎉 恭喜你已经掌握多表关联查询技能啦！下一篇我们回到 Node.js，学习用 mysql2 库在代码里连接 MySQL 并执行这些 SQL，把数据库真正接入项目。