SQL FULL OUTER JOIN：保留两张表的所有行

第 5.5 课 · 阅读时间：约 6 分钟

SQL FULL OUTER JOIN 返回两张表的所有行：匹配的行会组合在一起，任意一侧没有匹配的行也会保留，并在另一张表的列中填入 NULL。在本课中，你将学习它的语法、哪些数据库支持它，以及如何在 MySQL 和 MariaDB 中得到相同的结果。

LEFT JOIN 保留一张表中没有匹配的行，RIGHT JOIN 保留另一张表中的。FULL OUTER JOIN 两者都保留。它是比较两个列表的工具：哪些在两边都有，哪些只在第一个列表中，哪些只在第二个列表中。

FULL OUTER JOIN 返回什么

结果由三部分组成：

1. 匹配的行对，与 INNER JOIN 相同。
2. 左表中没有匹配的行，右表的列为 NULL。
3. 右表中没有匹配的行，左表的列为 NULL。

基本语法

SELECT left_table.column1, right_table.column2
FROM left_table
FULL OUTER JOIN right_table ON left_table.key_column = right_table.key_column;

FULL JOIN 和 FULL OUTER JOIN 是一样的。

示例：演员和客户

在 Sakila 数据库中，谁是演员、谁是客户、谁两者都是？在 PostgreSQL 或 SQL Server 中，按姓名进行全连接就能直接回答：

-- PostgreSQL, SQL Server
SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name;

每位演员出现一次，每位客户出现一次，两者都是的人则出现在同一行中，两侧都有值。

MySQL 和 MariaDB 中的 FULL OUTER JOIN

MySQL 和 MariaDB 没有 FULL OUTER JOIN，但可以用 UNION ALL 合并两个查询得到相同的结果：

1. LEFT JOIN 返回匹配的行以及只在左表中的行。
2. 带 WHERE left_key IS NULL 的 RIGHT JOIN 只补充只在右表中的行。

SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
LEFT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
UNION ALL
SELECT a.first_name, a.last_name, c.first_name, c.last_name
FROM actor AS a
RIGHT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL;

结果：798 行：200 位演员加 599 位客户，减去一位两者都是、出现在同一行中的人（JENNIFER DAVIS）。

你也可能看到这种替代写法使用 UNION 而不是 UNION ALL，并且没有 WHERE 条件。只有当结果中没有合理重复的行时它才正确，因为 UNION 会删除所有重复行，而且速度更慢。使用 UNION ALL 的写法才是精确的。你将在第 5.10 课中学习 UNION 和 UNION ALL。

用全连接比较列表

只要两个数据源应该包含相同的项目，全连接就很有用：

• 将数据库中的付款与银行对账单核对；
• 比较产品目录和库存清单；
• 迁移后查找只存在于两个系统之一中的记录。

如果只想保留差异，请过滤出其中一侧为 NULL 的行：

-- PostgreSQL, SQL Server：只是演员或只是客户的人
SELECT a.actor_id, c.customer_id
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL OR c.customer_id IS NULL;

要点：

• FULL OUTER JOIN 返回匹配的行，以及两张表中没有匹配的行。
• 缺失一侧的列为 NULL。
• PostgreSQL、SQL Server、Oracle 和 SQLite 3.39+ 支持它；MySQL 和 MariaDB 不支持。
• 在 MySQL 和 MariaDB 中：LEFT JOIN UNION ALL RIGHT JOIN ... WHERE left_key IS NULL。
• 全连接是比较两个列表的自然工具。

常见问题

MySQL 支持 FULL OUTER JOIN 吗？

不支持。MySQL 和 MariaDB 都不支持。请用 UNION ALL 合并一个 LEFT JOIN 和一个 RIGHT JOIN，并在第二个查询中只保留没有匹配的行（WHERE left_key IS NULL）。

FULL OUTER JOIN 和 CROSS JOIN 有什么区别？

FULL OUTER JOIN 按条件配对行，并保留两张表中没有匹配的行。CROSS JOIN 没有条件，返回所有行的组合，因此结果通常大得多。

为什么模拟 FULL OUTER JOIN 时要用 UNION ALL 而不是 UNION？

UNION 会删除重复行，包括数据中合理重复的行，而且查找重复行还需要额外的开销。在第二个查询中加上 IS NULL 过滤条件的 UNION ALL，返回的行与全连接完全相同。