[MySQL初阶]MySQL(7) 表的内外连接

标题：[MySQL初阶]MySQL(7)表的内外连接
@水墨不写bug

---

---

---

在 MySQL 中，连接（JOIN）用于根据两个或多个表之间的相关列组合行。内连接（INNER JOIN）和外连接（OUTER JOIN）的核心区别在于它们如何处理连接条件不匹配的行。

一. 内连接 (INNER JOIN)

• 作用： 返回两个表中连接条件都匹配的行。
• 结果集： 仅包含满足连接条件的行。如果一个表中的行在另一个表中没有匹配项，则该行不会出现在结果中。
• NULL 值： 结果集中不会出现 NULL 值（因为只返回匹配成功的行）。
• 语法：

SELECT ...
FROM table1
[INNER] JOIN table2 ON table1.column = table2.column;
-- `INNER` 关键字通常可以省略

内连接相当于之前写的两表做笛卡尔积后再通过where子句选择，意味着下面的这两句SQL是等价的：

#用之前的写法
select * from emp,dept where emp.deptno=dept.deptno and ename='lisi';
#用标准的内连接写法
select * from emp inner join dept on emp.deptno=dept.deptno and ename='lisi';

图示：

二. 外连接 (OUTER JOIN)

外连接强制保留一个或两个表的所有行，即使它们在另一个表中没有匹配项。根据保留哪个表的数据，外连接分为三种：

• a. 左外连接 (LEFT [OUTER] JOIN)
  • 作用： 返回左表的所有行，以及右表中满足连接条件的匹配行。如果右表没有匹配行，则右表列用 NULL 填充。
  • 结果集： 左表所有行 + 匹配的右表行（无匹配则右表部分为 NULL）。
  • NULL 值： 当右表没有匹配行时，结果集中右表对应的列会显示为 NULL。
  • 语法：

SELECT ...
FROM table1
LEFT [OUTER] JOIN table2 ON table1.column = table2.column;

图示：

Table A (左)     Table B (右)
+----+----+     +----+----+
| id | val|     | id | val|
+----+----+     +----+----+
| 1  | A  |     | 2  | X  |
| 2  | B  |     | 3  | Y  |
| 3  | C  |     | 4  | Z  |
+----+----+     +----+----+LEFT JOIN ON A.id = B.id:
+----+----+------+------+
| id | val| id   | val  |
+----+----+------+------+
| 1  | A  | NULL | NULL | <-- A 表行 1 保留，B 无匹配
| 2  | B  | 2    | X    |
| 3  | C  | 3    | Y    |
+----+----+------+------+

左外连接含义：返回左表（A）所有的记录，即使右表（B）中没有匹配的记录，B 表无匹配时结果为 NULL。
保留了左表 A 的所有行（id 1, 2, 3）。id 1 在 B 中无匹配，B 的列显示为 NULL。

• b. 右外连接 (RIGHT [OUTER] JOIN)
  • 作用： 返回右表的所有行，以及左表中满足连接条件的匹配行。如果左表没有匹配行，则左表列用 NULL 填充。
  • 结果集： 右表所有行 + 匹配的左表行（无匹配则左表部分为 NULL）。
  • NULL 值： 当左表没有匹配行时，结果集中左表对应的列会显示为 NULL。
  • 语法：

SELECT ...
FROM table1
RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;

图示：

Table A (左)     Table B (右)
+----+----+     +----+----+
| id | val|     | id | val|
+----+----+     +----+----+
| 1  | A  |     | 2  | X  |
| 2  | B  |     | 3  | Y  |
| 3  | C  |     | 4  | Z  |
+----+----+     +----+----+RIGHT JOIN ON A.id = B.id:
+------+------+----+----+
| id   | val  | id | val|
+------+------+----+----+
| 2    | B    | 2  | X  |
| 3    | C    | 3  | Y  |
| NULL | NULL | 4  | Z  | <-- B 表行 4 保留，A 无匹配
+------+------+----+----+

右外连接含义：返回右表（B）所有的记录，即使左表（A）中没有匹配的记录，A 表无匹配时结果为 NULL。
保留了右表 B 的所有行（id 2, 3, 4）。id 4 在 A 中无匹配，A 的列显示为 NULL。

• c. 全外连接 (FULL [OUTER] JOIN)
  • 作用： 返回左表和右表的所有行。只要其中一个表有匹配的行，就返回该行。 当某行在另一个表中没有匹配项时，另一个表的列用 NULL 填充。
  • 结果集： 左表所有行 + 右表所有行（无匹配的部分用 NULL 填充）。
  • NULL 值： 当左表或右表没有匹配行时，结果集中对应另一方的列会显示为 NULL。
  • 语法： MySQL 不直接支持 FULL OUTER JOIN。通常通过组合 LEFT JOIN 和 RIGHT JOIN 并使用 UNION（或 UNION ALL）来模拟实现：

SELECT ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNI0N [ALL] -- 使用 UNI0N ALL 会保留重复行（通常不应该有），UNI0N 会去重
SELECT ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column
WHERE table1.column IS NULL; -- 这个 WHERE 条件是为了只取 RIGHT JOIN 中左表为 NULL 的部分，避免重复

图示：

Table A         Table B
+----+----+     +----+----+
| id | val|     | id | val|
+----+----+     +----+----+
| 1  | A  |     | 2  | X  |
| 2  | B  |     | 3  | Y  |
| 3  | C  |     | 4  | Z  |
+----+----+     +----+----+FULL OUTER JOIN ON A.id = B.id:
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
| 1    | A    | NULL | NULL | <-- 仅存在于 A
| 2    | B    | 2    | X    |
| 3    | C    | 3    | Y    |
| NULL | NULL | 4    | Z    | <-- 仅存在于 B
+------+------+------+------+

全外连接含义：返回左右两表所有的记录，任何一方无匹配时用 NULL 填充。MySQL 需用 UNION 实现。

关键区别总结

特性	内连接 (INNER JOIN)	左外连接 (LEFT JOIN)	右外连接 (RIGHT JOIN)	全外连接 (FULL JOIN)
匹配行	返回	返回	返回	返回
左表不匹配行	不返回	返回 (NULL填充)	不返回	返回 (NULL填充)
右表不匹配行	不返回	不返回	返回 (NULL填充)	返回 (NULL填充)
结果来源	仅交集	左表全集 + 匹配的右表	右表全集 + 匹配的左表	左表全集 + 右表全集
NULL 值	不产生	在右表列产生	在左表列产生	在左表或右表列产生

三、 如何选择

• 需要两边都匹配的数据时： 用 INNER JOIN（最常见）。
• 需要主表所有记录 + 关联表匹配信息（即使关联表没有匹配项）时：
  • 主表在 FROM 后 / 第一个 JOIN 前 -> 用 LEFT JOIN。
  • 主表在 JOIN 关键字后 -> 用 RIGHT JOIN（相对较少用，通常通过调整表顺序改用 LEFT JOIN）。
• 需要两个表所有记录时： 用 FULL OUTER JOIN（MySQL 需模拟实现）。

示例场景：

• INNER JOIN: 查询所有下过订单的客户及其订单信息。
• LEFT JOIN: 查询所有客户及其订单信息（包括从未下过订单的客户）。
• RIGHT JOIN: 查询所有订单及其对应的客户信息（包括异常订单，其客户ID可能无效或被删除）。通常用 LEFT JOIN 并调换表顺序实现。
• FULL JOIN: 查询所有客户和所有订单（列出所有客户，无论是否有订单；列出所有订单，无论客户信息是否有效）。

---

完~
未经作者同意禁止转载