FULL OUTER JOIN 的作用与使用场景（检查表与表是否存在不同记录）

FULL OUTER JOIN 的作用与使用场景详解

概念解析

FULL OUTER JOIN 是 SQL 中用于合并两个表的关联查询方式，其核心特性是：

• 返回左表和右表中所有记录
• 当某条记录在另一表无匹配时，对应字段显示为 NULL
• 数学上可以理解为 LEFT JOIN + RIGHT JOIN 的并集

核心作用

1. 数据完整性验证
   用于确认两个数据集之间的关联完整性，例如：
   • 检查订单表与客户表是否存在孤立记录
   • 验证库存表与产品表的数据对应关系
2. 双向数据合并
   当需要同时保留两个表的独立记录时，例如：
   • 合并员工表和离职员工表生成完整人员档案
   • 整合历史数据与实时数据
3. 异常数据检测
   通过 NULL 值定位不匹配的异常数据，例如：
   • 找出没有对应部门的员工
   • 识别未关联到任何订单的客户

典型使用场景

-- 创建示例表
CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT);
CREATE TABLE departments (id INT, dept_name VARCHAR(20));
-- 插入测试数据
INSERT INTO employees VALUES
(1, '张三', 101),
(2, '李四', 102),
(3, '王五', NULL);  -- 无部门员工
INSERT INTO departments VALUES
(101, '技术部'),
(102, '市场部'),
(103, '财务部');  -- 无员工部门
-- 执行FULL OUTER JOIN
SELECT 
    e.name AS 员工姓名,
    COALESCE(d.dept_name, '未分配部门') AS 部门名称,
    e.dept_id AS 原始部门ID
FROM employees e
FULL OUTER JOIN departments d 
    ON e.dept_id = d.id;

结果说明

注意事项

1. MySQL 8.0 之前版本不支持原生语法，需通过 UNION 模拟：

   (SELECT ... FROM employees LEFT JOIN departments ...)
   UNION
   (SELECT ... FROM employees RIGHT JOIN departments ...)
   

2. 性能考量：在大数据表关联时，确保关联字段已建立索引
3. 可视化建议：使用 COALESCE 函数处理 NULL 值提升可读性

FULL OUTER JOIN业务中具体使用示例

在 SQL 查询中，FULL OUTER JOIN 是一种连接操作，它返回左表和右表中的所有行。如果某一边的表中没有匹配的行，则结果集中对应的列将包含 NULL 值。这种连接方式特别适用于需要合并两个表中所有数据的情况，即使某些数据在另一张表中没有对应的匹配项。

1. 示例说明

   以下 SQL 查询展示了如何使用 FULL OUTER JOIN 来比较两个角色 QA-PE-Engineer 和 QA-Engineer 所拥有的权限（功能）：

   SELECT
   	NVL(s1.ROLE_ID, 'QA-Engineer独有权限') AS ROLE_ID_1,
   	NVL2(s1.FUNC_NAME, s1.FUNC_NAME || ':' || s1.FUNC_DESC , '') AS FUNC_1_WITH_ASSEMBLY,
   	NVL(s2.ROLE_ID, 'QA-PE-Engineer独有权限') AS ROLE_ID_2,
   	NVL2(s2.FUNC_NAME, s2.FUNC_NAME || ':' || s2.FUNC_DESC , '') AS FUNC_2_WITH_ASSEMBLY
   FROM
   	(
   	SELECT
   		rf.FUNC_NAME,
   		rf.ROLE_ID,
   		df.FUNC_DESC
   	FROM
   		SCRMROLEFUNC rf
   	LEFT JOIN SCRMFUNCDFNT df ON
   		rf.FUNC_NAME = df.FUNC_NAME
   	WHERE
   		rf.ROLE_ID = 'QA-PE-Engineer'
   	GROUP BY
   		rf.FUNC_NAME,
   		rf.ROLE_ID,
   		df.FUNC_DESC
   ) s1
   FULL OUTER JOIN (
   	SELECT
   		rf.FUNC_NAME,
   		rf.ROLE_ID,
   		df.FUNC_DESC
   	FROM
   		SCRMROLEFUNC rf
   	LEFT JOIN SCRMFUNCDFNT df ON
   		rf.FUNC_NAME = df.FUNC_NAME
   	WHERE
   		rf.ROLE_ID = 'QA-Engineer'
   	GROUP BY
   		rf.FUNC_NAME,
   		rf.ROLE_ID,
   		df.FUNC_DESC
   ) s2 ON
   	s1.FUNC_NAME = s2.FUNC_NAME
   ORDER BY
   	(CASE
   		WHEN s1.FUNC_NAME IS NOT NULL
   			AND s2.FUNC_NAME IS NOT NULL THEN 1
   			WHEN s1.FUNC_NAME IS NOT NULL THEN 2
   			ELSE 3
   		END) DESC;
   

2. 查询结果解析

   该查询的主要目的是比较两个角色 QA-PE-Engineer 和 QA-Engineer 的权限，找出它们的共有权限和独有权限。

   • s1 子查询获取 QA-PE-Engineer 角色的功能名称、角色 ID 和功能描述。
   • s2 子查询获取 QA-Engineer 角色的功能名称、角色 ID 和功能描述。
   • 使用 FULL OUTER JOIN 连接两个子查询，基于 FUNC_NAME 字段。
   • 使用 NVL 函数处理 NULL 值，使得角色名称在没有匹配项时显示为“独有权限”。
   • 使用 NVL2 函数拼接功能名称和描述，若功能名称为 NULL 则返回空字符串。
   • 最后通过 CASE 表达式对结果进行排序，确保共有权限排在最前面，其次是 QA-PE-Engineer 的独有权限，最后是 QA-Engineer 的独有权限。

3. 查询结果示例

   ROLE_ID_1	FUNC_1_WITH_ASSEMBLY	ROLE_ID_2	FUNC_2_WITH_ASSEMBLY
   QA-PE-Engineer	func1:功能1描述	QA-Engineer	func1:功能1描述
   QA-PE-Engineer	func2:功能2描述	QA-Engineer独有权限
   QA-Engineer独有权限		QA-Engineer	func3:功能3描述

更多精彩文章可扫码关注公主号查看：