mysql菜鸟教程
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（员工表）
emp_id
emp_name
dept_id
1
张三
101
2
李四
102
3
王五
NULL
departments（部门表）
dept_id
dept_name
101
技术部
102
市场部
103
财务部
我们想要得到所有员工和所有部门的完整列表，包括：
-
有部门的员工（匹配）
-
无部门的员工（部门信息为NULL）
-
无员工的部门（员工信息为NULL）
这正是全外连接的目标。
模拟步骤：
-
先取左连接：employees LEFT JOIN departments，得到所有员工及其部门信息（无部门的员工也包含）。
-
再取右连接：employees RIGHT JOIN departments，得到所有部门及其员工信息（无员工的部门也包含）。
-
将两个结果用 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;
结果：
emp_id
emp_name
dept_id
dept_name
1
张三
101
技术部
2
李四
102
市场部
3
王五
NULL
NULL
NULL
NULL
NULL
财务部
这样就得到了全外连接的效果。
三、全外连接的应用场景
全外连接常用于需要完整展示两个实体所有信息的场景，例如：
-
数据合并：将两个来源的数据合并，确保不丢失任何一方。
-
差异对比：找出两张表的差异（匹配的行、左表独有的行、右表独有的行）。
-
数据完整性检查：发现孤儿记录（例如，无员工的部门和无部门的员工）。
四、使用 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; -- 匹配的行
这样可以清晰区分三类数据。
六、与其他连接的对比
连接类型
返回行数
左表无匹配
右表无匹配
匹配行
INNER JOIN
匹配行数
丢弃
丢弃
保留
LEFT JOIN
左表行数
保留，右表补 NULL
保留
RIGHT JOIN
右表行数
保留，左表补 NULL
保留
FULL OUTER JOIN
左表行数 + 右表独有行数
保留
保留
保留
七、全外连接与性能
模拟全外连接需要执行两个连接操作和一个 UNION，性能可能不如原生支持。对于大数据量，应谨慎使用，并考虑是否有其他更优的查询方式。
八、练习与思考
-
使用 students 和 classes 表，模拟全外连接查询所有学生和所有班级，包括无班级的学生和无学生的班级。
-
如何用全外连接找出那些既没有学生也没有班级的孤立记录？（此题不太合理，因为学生和班级本来就是两个独立实体，全外连接会展示所有，孤立记录指两边都没有关联？其实全外连接会展示所有行，你可以通过筛选两边都为 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 根据需求决定。
-
全外连接适合需要完整展示两个实体所有信息的场景。
发表评论
所有评论