第 06 章：多表关联与子查询

zjc 于 2026-01-06 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。真实业务数据分散在多张表中。JOIN 负责把关系重新组合，子查询负责表达“基于另一批结果的查询条件”。本章重点是理解不同 JOIN 的语义、子查询执行方式和生产 SQL 的复杂度控制。

6.1 准备示例数据

沿用第 02 章的表，先补充几条订单：

INSERT INTO orders
  (order_no, user_id, merchant_id, status, total_amount, paid_at)
VALUES
  ('20260825000011', 1, 1, 'PAID', 25.50, NOW(3)),
  ('20260825000012', 1, 2, 'CREATED', 79.00, NULL),
  ('20260825000013', 2, 1, 'PAID', 17.00, NOW(3)),
  ('20260825000014', 3, 3, 'CANCELED', 528.00, NULL);

INSERT INTO order_items
  (order_id, product_id, product_name, unit_price, quantity, subtotal)
VALUES
  (1, 1, 'Apple', 8.50, 3, 25.50),
  (2, 3, 'MySQL Book', 79.00, 1, 79.00),
  (3, 2, 'Milk', 12.00, 1, 12.00),
  (3, 1, 'Apple', 8.50, 1, 8.50),
  (4, 4, 'Keyboard', 399.00, 1, 399.00),
  (4, 5, 'Mouse', 129.00, 1, 129.00);

6.2 INNER JOIN

查询订单和用户信息：

SELECT
  o.order_no,
  o.status,
  o.total_amount,
  u.username,
  u.mobile
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'PAID';

INNER JOIN 只返回两边都能匹配的行。如果某个 user_id 在 users 表不存在，这条订单不会出现。

多表关联：

SELECT
  o.order_no,
  u.username,
  m.merchant_name,
  oi.product_name,
  oi.quantity,
  oi.subtotal
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
INNER JOIN merchants AS m ON m.id = o.merchant_id
INNER JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status = 'PAID'
ORDER BY o.id, oi.id;

订单和订单明细是一对多关系，JOIN 后订单金额会重复出现，不能直接 SUM(o.total_amount)。

按订单粒度汇总：

SELECT
  u.username,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(o.total_amount) AS paid_amount
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'PAID'
GROUP BY u.username;

6.3 LEFT JOIN

查询用户及其订单，没有订单的用户也要显示：

SELECT
  u.id,
  u.username,
  o.order_no,
  o.status,
  o.total_amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.status = 1
ORDER BY u.id, o.id;

没有订单的用户，o.order_no 等右侧字段为 NULL。

统计每个用户的订单数：

SELECT
  u.username,
  COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.username;

这里必须使用 COUNT(o.id)，因为 COUNT(*) 会把没有订单的用户也统计为 1。

6.4 WHERE 对 LEFT JOIN 的影响

以下查询语义会退化：

SELECT u.username, o.order_no
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'PAID';

没有订单的用户右侧字段是 NULL，不满足 o.status = 'PAID'，会被过滤掉，效果接近 INNER JOIN。

如果要求“所有用户，以及他们的已支付订单，没有则显示 NULL”，应把右侧条件放到 ON：

SELECT u.username, o.order_no, o.status
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
 AND o.status = 'PAID'
ORDER BY u.id;

简单记法：

左表过滤条件可以放 WHERE；
右表必须保留 NULL 的过滤条件应放 ON；
放置位置代表不同业务语义，不要机械套用。

6.5 RIGHT JOIN 与 FULL JOIN

右连接：

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

等价于：

SELECT o.order_no, u.username
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

MySQL 8.0 不支持 FULL OUTER JOIN。可以用 LEFT JOIN UNION RIGHT JOIN 模拟：

SELECT u.username, o.order_no
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id

UNION

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

团队规范通常统一使用 LEFT JOIN，减少阅读成本。

6.6 CROSS JOIN

CROSS JOIN 生成笛卡尔积：

SELECT
  u.username,
  m.merchant_name
FROM users AS u
CROSS JOIN merchants AS m;

如果左表 10 万行，右表 5 万行，结果就是 50 亿行。生产中出现无条件笛卡尔积通常是严重错误。

6.7 JOIN 条件与数据质量

JOIN 依赖关联键质量。常见问题：

问题	现象	处理
类型不一致	隐式转换，索引失效	关联字段类型一致
字符集不一致	索引无法有效使用	表间字符集和排序规则统一
脏数据	匹配不到或重复匹配	约束加对账
一对多	汇总翻倍	先聚合或分粒度统计
重复维度表	数据膨胀	维度表保证唯一

检查孤儿订单：

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

6.8 子查询分类

子查询可以出现在不同位置：

位置	示例	说明
标量子查询	`SELECT (SELECT MAX(price) FROM products)`	返回单行单列
列子查询	`WHERE id IN (...)`	返回一列多行
行子查询	`WHERE (a, b) = (...)`	返回一行多列
表子查询	`FROM (...) AS t`	派生表
EXISTS 子查询	`WHERE EXISTS (...)`	判断存在性

6.8.1 标量子查询

查询价格高于平均价的商品：

SELECT id, product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

标量子查询如果放在 SELECT 列表中且对外层每一行执行，可能导致大量重复计算。多数简单场景优化器可以处理，但复杂 SQL 需要看执行计划。

6.8.2 IN 子查询

查询有订单的用户：

SELECT id, username
FROM users
WHERE id IN (SELECT user_id FROM orders);

查询没有订单的用户：

SELECT id, username
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

NOT IN 遇到 NULL 时风险很高。以下查询结果为空：

SELECT 1
WHERE 1 NOT IN (2, NULL);

因为 1 <> NULL 是 UNKNOWN。如果子查询可能返回 NULL，应显式过滤：

SELECT id, username
FROM users
WHERE id NOT IN (
  SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

或使用 NOT EXISTS。

6.8.3 EXISTS 与 NOT EXISTS

有订单的用户：

SELECT u.id, u.username
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

没有订单的用户：

SELECT u.id, u.username
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

EXISTS 关注是否存在匹配，通常建议：

关联列有索引；
子查询 SELECT 1 即可；
反向关系优先考虑 NOT EXISTS；
不要在外层大表上执行无法下推的相关子查询。

6.9 派生表与 CTE

派生表：

SELECT
  t.user_id,
  t.order_count
FROM (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) AS t
WHERE t.order_count >= 2;

CTE 让复杂查询更清晰：

WITH user_order_stats AS (
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_paid
  FROM orders
  WHERE status = 'PAID'
  GROUP BY user_id
)
SELECT
  u.username,
  s.order_count,
  s.total_paid
FROM user_order_stats AS s
INNER JOIN users AS u ON u.id = s.user_id
WHERE s.order_count >= 1
ORDER BY s.total_paid DESC;

MySQL 8.0 开始支持 CTE，5.7 不支持。

递归 CTE 查询层级：

CREATE TABLE regions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id BIGINT UNSIGNED NULL,
    region_name VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

WITH RECURSIVE region_tree AS (
  SELECT id, parent_id, region_name, 1 AS level
  FROM regions
  WHERE parent_id IS NULL

  UNION ALL

  SELECT r.id, r.parent_id, r.region_name, rt.level + 1
  FROM regions AS r
  INNER JOIN region_tree AS rt ON r.parent_id = rt.id
)
SELECT * FROM region_tree;

递归查询必须防止无限循环。MySQL 有默认递归深度限制，也可以设置：

SET SESSION cte_max_recursion_depth = 1000;

生产中应先验证数据不存在环，再执行递归查询。

6.10 UNION 与 UNION ALL

UNION 去重：

SELECT user_id FROM orders
UNION
SELECT id FROM users;

UNION ALL 不去重：

SELECT user_id, 'ORDER' AS source FROM orders
UNION ALL
SELECT id, 'USER' AS source FROM users;

如果没有去重需求，使用 UNION ALL，避免额外去重成本。

列数和类型必须匹配：

SELECT order_no AS biz_no, total_amount AS amount
FROM orders
UNION ALL
SELECT payment_no, amount
FROM payment_records;

6.11 查询复杂度治理

复杂 SQL 常见问题：

十几张表 JOIN；
子查询嵌套五六层；
SELECT 中包含重逻辑；
一个接口返回所有字段；
报表、交易、详情共用一条 SQL；
没有人能解释执行计划。

治理建议：

问题	处理方式
JOIN 太多	拆接口、拆查询、使用冗余快照
报表复杂	走只读库、ClickHouse 或预聚合
子查询重复	使用 CTE 命名并确认只物化一次
结果太大	分页、游标、限制字段
跨域聚合	数仓或分析引擎处理
代码难维护	中间结果落临时表或任务表

6.12 执行计划初看

查看 JOIN 计划：

EXPLAIN
SELECT u.username, o.order_no
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.user_id = 1;

重点关注：

字段	意义
`type`	访问类型，`ref` 通常好于 `range`，`ALL` 是全表扫描
`key`	实际使用的索引
`rows`	预估扫描行数
`filtered`	条件过滤后估算比例
`Extra`	是否 Using temporary、Using filesort

MySQL 8.0.18+ 可以查看真实执行：

EXPLAIN ANALYZE
SELECT u.username, o.order_no
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.user_id = 1;

第 08 章会系统展开。

本章小结

JOIN 的语义由连接类型和条件位置共同决定。INNER JOIN 取交集，LEFT JOIN 保留左表，右侧条件放 ON 或 WHERE 会改变结果。子查询分为标量、IN、EXISTS、派生表和 CTE，写法要结合数据量、索引和空值风险。复杂查询应优先保证语义清晰、可索引、可验证，再考虑语法精简。

思考题

LEFT JOIN 下右表条件放在 ON 和 WHERE 有什么区别？
为什么统计用户订单数要使用 COUNT(o.id) 而不是 COUNT(*)？
NOT IN 包含 NULL 时有什么风险？
一对多 JOIN 后为什么直接 SUM 主表金额会翻倍？
将一个五层嵌套子查询改写为 CTE，并解释每层业务含义。