[MySQL初阶]MySQL(7) 表的内外连接
标题：[MySQL初阶]MySQL(7)表的内外连接
@水墨不写bug
文章目录
- 一. 内连接 (INNER JOIN)
- 二. 外连接 (OUTER JOIN)
- 关键区别总结
- 三、 如何选择
在 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: 查询所有客户和所有订单（列出所有客户，无论是否有订单；列出所有订单，无论客户信息是否有效）。
完~
未经作者同意禁止转载
