人人都会AI编程

9.3 联表查询：内连接、左连接、右连接、全连接、自连接

更新时间：2026-07-11

在实际业务中，数据往往分散在多张表中。联表查询（JOIN）就是将多张表按照某种关联条件横向拼接在一起，让你能在一个查询中拿到完整的信息。掌握 JOIN 是写实用 SQL 的基本功，但很多开发者只在“能用”层面使用，对连接类型的差异和性能影响理解不深，容易写出结果不准或效率低下的语句。

本节从最常用的内连接开始，逐步覆盖左连接、右连接、全连接和自连接，不仅讲语法，更强调各自的适用场景和常见的踩坑点。

9.3.1 内连接（INNER JOIN）

内连接是使用频率最高的连接方式，它基于连接条件，只返回两表之间能匹配上的记录。

基本语法是：

SELECT 列名
FROM 表A
INNER JOIN 表B ON 表A.关联列 = 表B.关联列;

其中 INNER JOIN 可以简写为 JOIN。

举个例子，有两张表 users 和 orders：

@@SNAPSHOT_BLOCK_1@@`

查询每个订单对应的用户名和金额：

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

结果只包含有订单的用户（张三 2 条，李四 1 条），王五因为没有订单，不会出现在结果中。这就是内连接的“交集”特性。

内连接的执行逻辑：MySQL 通常会选择一张表作为驱动表，扫描该表的每一行，然后去另一张表里根据连接条件查找匹配行。如果连接条件列上有索引（比如 orders.user_id 上建有索引），查找效率会很高，否则会退化为全表扫描嵌套。优化器会自动选择代价较小的表作为驱动表，但作为开发者，可以用 EXPLAIN 查看执行计划，判断优化器是否做出了正确决策。

实际使用中需要注意：

  • INNER JOIN 和 WHERE 子句中的过滤条件要分开理解：ON 中的条件是连接条件，决定两表如何匹配；WHERE 是连接后的过滤条件。对于内连接，将过滤条件放在 ON 或 WHERE 中结果相同，但语义上建议将连接条件写 ON，过滤条件写 WHERE，这样更清晰。
  • 如果两张表很大，尽量避免生成庞大的中间结果集，要用索引加速关联。

9.3.2 左连接（LEFT JOIN）

左连接又叫左外连接（LEFT OUTER JOIN），它会返回左表中的所有记录，即使右表中没有匹配的行。如果右表无匹配，结果集中右表的列全部为 NULL。

语法：

SELECT 列名
FROM 左表
LEFT JOIN 右表 ON 连接条件;

还是用上面的 users 和 orders 表，想要列出所有用户以及他们的总订单金额，即使是王五这种没有订单的用户也要列出：

SELECT u.name, SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

结果会是：

+----------+--------------+
| name     | total_amount |
+----------+--------------+
| 张三     | 250.00       |
| 李四     | 200.00       |
| 王五     | NULL         |
+----------+--------------+

左连接最常见的用途就是“主表全量 + 附表匹配”，比如报表场景中必须显示所有维度项，哪怕指标为 0 或 NULL。

重要注意事项：

  • 过滤条件的位置严重影响结果。如果你把对右表的过滤条件写在 WHERE 子句中，例如 WHERE o.amount > 100，那么那些没有匹配订单的用户（右表列为 NULL）会因为条件不满足而被过滤掉，左连接实际退化成内连接。正确做法是把右表的过滤条件写在 ON 子句中：LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100，这样仍然会保留所有用户，只是不符合 amount > 100 的订单不参与连接。示例如下：

错误写法（导致漏掉王五）：

  SELECT u.name, o.amount
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE o.amount > 100;   -- 王五被过滤掉了

正确写法（保留所有用户）：

  SELECT u.name, o.amount
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;
  • 左连接的性能考量：左连接中，左表通常是驱动表，右表是被驱动表。如果右表很大且连接条件没有索引，左连接也会很慢。但因为是逐一匹配，索引优化同样关键。

9.3.3 右连接（RIGHT JOIN）

右连接就是左连接的反向操作：返回右表中的所有记录，左表中没有匹配的用 NULL 填充。

语法：

SELECT 列名
FROM 左表
RIGHT JOIN 右表 ON 连接条件;

它的效果完全等同于将表的顺序调换后的左连接。也就是说：

SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

等价于：

SELECT u.name, o.amount
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

在实际项目中，右连接很少被使用。因为左连接已经能满足需求，而且从可读性上说，始终把“主表”放在左边、用左连接表达是一种广为接受的编码习惯。如果你在维护别人的代码时看到了右连接，不妨将它改写为左连接，让逻辑更直观。

9.3.4 全连接（FULL JOIN）与 MySQL 的替代实现

全连接（FULL OUTER JOIN）会返回两表的并集：既能匹配上的行，也包含左表单独有的行（右表补 NULL），以及右表单独有的行（左表补 NULL）。

遗憾的是，MySQL 目前并不支持 FULL JOIN 语法（一直到 8.0 版本也没有直接支持）。但业务中偶尔会有这样的需求，例如想要对比两张表的差异，或者合并两个来源的数据。这时候可以用 LEFT JOIN + UNION + RIGHT JOIN 来模拟。

假设有 table_a 和 table_b，要得到全连接的结果：

SELECT *
FROM table_a
LEFT JOIN table_b ON table_a.id = table_b.id
UNION
SELECT *
FROM table_a
RIGHT JOIN table_b ON table_a.id = table_b.id;

因为 UNION 会去重，如果两张表确实存在相同的匹配行，也只会保留一条。如果明确不需要去重或者知道没有重复，可以用 UNION ALL 提高效率，但这样对于匹配上的行会重复出现（左侧连接和右侧连接各产生一次），所以通常还是会使用 UNION。

另外一种更简洁的写法是：直接查询左连接和右连接中其中一方为 NULL 的部分，再合并:

SELECT *
FROM table_a
LEFT JOIN table_b ON table_a.id = table_b.id
UNION ALL
SELECT *
FROM table_a
RIGHT JOIN table_b ON table_a.id = table_b.id
WHERE table_a.id IS NULL;

这种写法避免了去重开销，但理解起来复杂一些。实际中按需选择。

9.3.5 自连接（Self JOIN）

自连接不是一种特定的连接类型，而是一张表与自己进行连接的技术。它用到的还是前面介绍的内连接、左连接等，只是把同一张表用不同的别名当做两张表来处理。

典型的场景是树形结构或层级关系的查询。比如员工表 employees 中有 id、name、manager_id（指向上级的 id），要列出每个员工及其上级的名字：

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

这里 e1 作为员工（左表），e2 作为上级（右表），用左连接保证即使没有上级（如 CEO）的员工也能显示出来，上级列为 NULL。

自连接的要点：

  • 别名是必须的：同一张表出现两次，必须使用不同的别名（e1、e2）来区分，否则数据库无法理解。
  • 自连接的底层原理并没有特殊之处：MySQL 仍然把别名当做两张独立的表来处理，所以在连接列上加索引依然重要（例如 manager_id 应该建有索引）。
  • 递归查询的更优解法：对于多层级的树状结构（例如组织架构层级、商品分类父子级），自连接只能拿到直接上级，若需要递归遍历全部祖先或子孙，在 MySQL 8.0 以前需要写存储过程或者多次自连接。但在 MySQL 8.0 中可以使用递归 CTE（公用表表达式） 来处理，语法更简洁，性能也更好。示例：
  WITH RECURSIVE cte AS (
      SELECT id, name, manager_id FROM employees WHERE id = 1  -- 起点
      UNION ALL
      SELECT e.id, e.name, e.manager_id
      FROM employees e
      INNER JOIN cte ON e.manager_id = cte.id
  )
  SELECT * FROM cte;

这比多层自连接要清晰和高效得多。

9.3.6 联表查询的性能与注意事项

联表查询虽然强大，但如果滥用或者设计不当，会成为性能杀手。总结几条必须遵守的实用规则：

  1. 关联列务必要有索引：特别是被驱动表的连接列（ON 子句中参照的列）。如果 orders.user_id 上没有索引，LEFT JOIN 在匹配时就要全表扫描 orders，性能极差。
  2. 尽量减少连接的表数量：超过三张表以上的关联建议反思表结构设计是否合理，能否提前通过冗余或汇总减少关联。每个连接都会增加复杂度，也会增加优化器选错计划的风险。
  3. 注意连接类型的语义：根据业务需求选择内连接还是外连接，不要把过滤条件错放在 WHERE 里导致外连接失效。养成写 LEFT JOIN 时把右表过滤条件放 ON 里的习惯。
  4. 避免笛卡尔积：忘记写 ON 条件或者条件写错，会导致笛卡尔积（行数相乘），瞬间耗尽资源。在连接多表时务必检查每个 JOIN 都有明确的 ON 条件。
  5. 用 EXISTS 替代部分连接：有时候你只需要检查另外一张表中是否存在匹配记录，而不需要返回那张表的列，此时用 EXISTS 子查询往往比 JOIN 更高效，因为 EXISTS 会在找到第一条匹配时立即返回，不会生成冗余结果集。例如：
   SELECT * FROM users u
   WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

联表查询是 SQL 中连接数据的核心手段，理解各种连接类型的“集合逻辑”比死记语法更重要。建议你在写复杂查询之前，先在脑子里画出每张表经过连接后的预期行数变化，再用 EXPLAIN 验证执行计划，这样就能写出既正确又高效的连接语句。