LumantoBlog

Appearance

MySQL 第五章：多表查询与 JOIN ​

本章目标：掌握多表查询的核心写法，理解内连接、外连接、自连接、子查询的使用场景，能够写出业务可用 SQL。

1. 为什么需要多表查询 ​

实际业务中，数据通常按范式拆分在多张表里：

  • 用户信息在 users
  • 订单信息在 orders
  • 订单明细在 order_items

所以查询一个完整业务结果，往往需要把多张表“拼起来”。

graph LR
    A["users<br/>用户表"] --> B["orders<br/>订单表"]
    B --> C["order_items<br/>订单明细表"]

2. 准备测试表与数据 ​

sql
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL
);

INSERT INTO users (username) VALUES ('张三'), ('李四'), ('王五');

INSERT INTO orders (user_id, total_amount, created_at) VALUES
(1, 299.00, '2026-04-01 10:00:00'),
(1, 159.00, '2026-04-03 09:30:00'),
(2, 520.00, '2026-04-04 11:20:00');

INSERT INTO order_items (order_id, product_name, price, quantity) VALUES
(1, '机械键盘', 299.00, 1),
(2, '鼠标', 79.00, 1),
(2, '鼠标垫', 80.00, 1),
(3, '显示器', 520.00, 1);

3. JOIN 核心概念 ​

JOIN 的本质：按连接条件把两张（或多张）表的行组合起来。

  • 连接条件通常写在 ON 后面
  • 多数情况下通过主键与外键关联
  • 推荐给表起别名，提高可读性

基本结构：

sql
SELECT 字段列表
FROM 表1 t1
JOIN 表2 t2 ON t1.id = t2.xxx_id;

4. INNER JOIN（内连接） ​

只返回两张表都匹配成功的记录。

4.1 查询订单及所属用户 ​

sql
SELECT o.id AS order_id, u.username, o.total_amount, o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

4.2 查询订单明细 + 用户名 ​

sql
SELECT o.id AS order_id,
       u.username,
       oi.product_name,
       oi.price,
       oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id;

5. LEFT JOIN（左外连接） ​

返回左表全部数据，右表匹配不到则补 NULL。

5.1 查询所有用户和其订单（无订单也显示） ​

sql
SELECT u.id, u.username, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.id;

5.2 找出“没有下单”的用户 ​

sql
SELECT u.id, u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

6. RIGHT JOIN（右外连接） ​

返回右表全部数据，左表匹配不到则补 NULL。

实战中使用率低于 LEFT JOIN，通常可通过调换表顺序改写成 LEFT JOIN。

sql
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

7. 多表 JOIN 与聚合统计 ​

7.1 统计每位用户的订单数和消费总额 ​

sql
SELECT u.id,
       u.username,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

7.2 每个订单的商品件数总和 ​

sql
SELECT o.id AS order_id,
       SUM(oi.quantity) AS total_quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

8. 自连接（Self Join） ​

当一张表内部有层级关系（如员工与上级）时，可让同一张表 JOIN 自己。

示例结构：employees(id, name, manager_id)

sql
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

9. 子查询与 JOIN 对比 ​

9.1 子查询示例：查询消费金额大于平均值的订单 ​

sql
SELECT id, user_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

9.2 JOIN 与子查询怎么选 ​

  • 简单过滤、单值比较：子查询直观
  • 多表字段联动展示：JOIN 更清晰
  • 大数据量场景：通常优先考虑 JOIN + 合理索引

10. JOIN 执行流程（理解版） ​

flowchart LR
    A[FROM 主表] --> B[JOIN 关联表]
    B --> C[ON 连接条件]
    C --> D[WHERE 过滤]
    D --> E[GROUP BY 分组]
    E --> F[HAVING 过滤分组]
    F --> G[SELECT 字段]
    G --> H[ORDER BY 排序]
    H --> I[LIMIT 分页]

11. 面试高频点 ​

11.1 ON 和 WHERE 在 JOIN 里的区别 ​

  • ON：定义“怎么连表”
  • WHERE：定义“连完后保留哪些行”

11.2 为什么 LEFT JOIN 后要用 IS NULL ​

因为未匹配数据补的是 NULL，不能写 = NULL，必须 IS NULL。

11.3 多表查询慢怎么排查 ​

  1. 检查连接字段是否有索引
  2. 用 EXPLAIN 看执行计划
  3. 减少返回字段，避免 SELECT *
  4. 先过滤再 JOIN，降低中间结果集

12. 练习题 ​

  1. 查询每个用户最近一笔订单时间。
  2. 查询每个订单对应的商品名称列表。
  3. 找出从未下单的用户。
  4. 统计每个用户购买商品总件数，并按件数降序。
  5. 使用子查询查询“订单金额高于全站平均值”的订单。

13. 本章小结 ​

  • 多表查询是关系型数据库的核心能力，JOIN 是日常高频技能。
  • INNER JOIN 查交集，LEFT JOIN 保留左表全量是最常用组合。
  • 会写 SQL 不够，理解执行顺序和索引思维，才能写出“又对又快”的查询。