sql-full-joins

SQL - FULL OUTER JOIN

理解 FULL OUTER JOIN

FULL OUTER JOIN，通常也写作 FULL JOIN，它结合了 LEFT JOIN（左连接）和 RIGHT JOIN（右连接）的结果。最终连接的表将包含来自两个表的所有记录。对于在另一个表中有匹配项的行，其列将被合并；对于没有匹配项的行，另一个表中的相应列将填充 NULL 值。

可以将其视为一种从两个表中查看完整统一列表的方式，同时突出显示它们之间的匹配和不匹配记录。

FULL OUTER JOIN 对于数据核对任务特别有用，在此类任务中，您需要查找存在于一个系统（表）中但缺失于另一个系统中的实体。

语法和示例

让我们考虑两个表：employees（员工，被分配到项目）和 projects（项目，可能分配有员工，也可能没有）。

employees 表：

id | name
---+----------
1  | Alice
2  | Bob
3  | Charlie  -- 未分配

projects 表：

id | project_name  | employee_id
---+---------------+-------------
101| Project Alpha | 1
102| Project Beta  | 2
103| Project Gamma | NULL -- 未分配员工

语法

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
FULL OUTER JOIN projects AS p ON e.id = p.employee_id;

输出

结果显示了所有员工和所有项目。

employee_name | project_name
---------------+----------------
Alice         | Project Alpha
Bob           | Project Beta
Charlie       | NULL             -- 没有项目的员工
NULL          | Project Gamma    -- 没有员工的项目

在 MySQL 中模拟 FULL JOIN

MySQL 不直接支持 FULL OUTER JOIN 语法。但是，您可以通过结合 LEFT JOIN 和 RIGHT JOIN 以及 UNION 运算符来获得完全相同的结果。

语法 (MySQL 模拟)

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
LEFT JOIN projects AS p ON e.id = p.employee_id

UNION

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
RIGHT JOIN projects AS p ON e.id = p.employee_id;

LEFT JOIN 获取所有员工及其项目。RIGHT JOIN 获取所有项目及其员工。UNION 将这两个结果集合并，并自动删除重复行（在两个连接中都匹配的行），从而产生与真正的 FULL OUTER JOIN 相同的输出。

连接多个表

您可以链式连接 FULL JOIN 来组合两个以上的表。逻辑会顺序扩展：第一个连接的结果成为下一个连接的左侧。

SELECT ...
FROM tableA
FULL JOIN tableB ON tableA.id = tableB.a_id
FULL JOIN tableC ON tableB.id = tableC.b_id;

以这种方式连接多个表时要谨慎。逻辑可能会变得复杂，并且 NULL 值的数量可能会增加，使得结果集难以解释。

将 FULL JOIN 与 WHERE 子句结合使用

您可以使用 WHERE 子句来过滤 FULL OUTER JOIN 的结果。这对于查找存在于一个表但不存在于另一个表中的记录非常强大。

示例：查找没有项目的员工

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
FULL OUTER JOIN projects AS p ON e.id = p.employee_id
WHERE p.id IS NULL;

输出

employee_name | project_name
---------------+----------------
Charlie       | NULL

示例：查找没有员工的项目

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
FULL OUTER JOIN projects AS p ON e.id = p.employee_id
WHERE e.id IS NULL;

输出

employee_name | project_name
---------------+----------------
NULL          | Project Gamma