11.5 全外连接（FULL OUTER JOIN）

      在介绍了内连接、左连接和右连接之后，我们来看一种更全面的连接方式——全外连接（FULL OUTER JOIN）。它返回两个表中的所有记录，无论是否匹配，不匹配的一侧用 NULL 填充。简单来说，全外连接 = 左连接 + 右连接（去重）。

一、什么是全外连接？

全外连接返回左表和右表的全部行，相当于左连接和右连接的并集。如果某一行在左表中有但在右表中没有匹配，则右表部分显示 NULL；如果某一行在右表中有但在左表中没有匹配，则左表部分显示 NULL。

用集合理解：

• 表A 和 表B 做全外连接，结果 = A ∪ B（全集），缺失部分补 NULL。

标准 SQL 语法

SELECT 列名
FROM 表1
FULL OUTER JOIN 表2 ON 连接条件;

二、MySQL 对全外连接的支持

MySQL 不直接支持 FULL OUTER JOIN 语法。如果直接使用，会报语法错误。但我们可以通过组合 LEFT JOIN 和 RIGHT JOIN 并使用 UNION 来模拟实现。

模拟全外连接的方法

假设我们有两张表：employees（员工表）和 departments（部门表）。员工表中有员工及其所属部门ID，部门表中有部门ID和部门名称。有些员工可能没有分配部门（部门ID为NULL），有些部门可能还没有员工。

employees（员工表）

departments（部门表）

我们想要得到所有员工和所有部门的完整列表，包括：

• 有部门的员工（匹配）

• 无部门的员工（部门信息为NULL）

• 无员工的部门（员工信息为NULL）

这正是全外连接的目标。

模拟步骤：

1. 先取左连接：employees LEFT JOIN departments，得到所有员工及其部门信息（无部门的员工也包含）。

2. 再取右连接：employees RIGHT JOIN departments，得到所有部门及其员工信息（无员工的部门也包含）。

3. 将两个结果用 UNION 合并，并去除重复行（UNION 默认去重，若需保留所有行可用 UNION ALL，但此处通常要去重）。

模拟 SQL

-- 左连接部分：所有员工及其部门
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION

-- 右连接部分：所有部门及其员工
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

结果：

这样就得到了全外连接的效果。

三、全外连接的应用场景

全外连接常用于需要完整展示两个实体所有信息的场景，例如：

• 数据合并：将两个来源的数据合并，确保不丢失任何一方。

• 差异对比：找出两张表的差异（匹配的行、左表独有的行、右表独有的行）。

• 数据完整性检查：发现孤儿记录（例如，无员工的部门和无部门的员工）。

四、使用 UNION 与 UNION ALL 的注意事项

• 使用 UNION 会去除重复行。在左连接和右连接的并集中，如果存在完全相同的行（即某一行在两个连接结果中都出现），UNION 会保留一份。这种情况通常发生在那些在两边都匹配的行上（例如，有员工的部门）。UNION 的去重是合理的。

• 如果想保留所有重复行（通常不需要），可以使用 UNION ALL，但会导致结果中出现重复的匹配行。

五、模拟全外连接并标记来源

有时我们不仅需要全外连接的结果，还想知道某行来自哪边。可以通过添加一个额外的列来标记：

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, 'left_only' AS source
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL   -- 只取左表独有的行

UNION ALL

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, 'right_only' AS source
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL    -- 只取右表独有的行

UNION ALL

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, 'matched' AS source
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;   -- 匹配的行

这样可以清晰区分三类数据。

六、与其他连接的对比

七、全外连接与性能

模拟全外连接需要执行两个连接操作和一个 UNION，性能可能不如原生支持。对于大数据量，应谨慎使用，并考虑是否有其他更优的查询方式。

八、练习与思考

1. 使用 students 和 classes 表，模拟全外连接查询所有学生和所有班级，包括无班级的学生和无学生的班级。

2. 如何用全外连接找出那些既没有学生也没有班级的孤立记录？（此题不太合理，因为学生和班级本来就是两个独立实体，全外连接会展示所有，孤立记录指两边都没有关联？其实全外连接会展示所有行，你可以通过筛选两边都为 NULL 来找出什么？两边都为 NULL 不可能，因为一行至少有一边有数据。实际上，全外连接不会产生两边都为 NULL 的行。）

答案：

-- 1. 全外连接模拟
SELECT s.student_id, s.name, s.class_id, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id

UNION

SELECT s.student_id, s.name, s.class_id, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.class_id;

九、小结

• 全外连接 返回两个表的全部记录，不匹配的一侧补 NULL。

• MySQL 不直接支持 FULL OUTER JOIN，需通过 LEFT JOIN UNION RIGHT JOIN 模拟。

• 使用 UNION（去重）或 UNION ALL 根据需求决定。

• 全外连接适合需要完整展示两个实体所有信息的场景。