如何在 MySQL 中执行 FULL OUTER JOIN？

Question

我想在 MySQL 中执行完全外连接。这可能吗？ MySQL 支持全外连接吗？

Answer 1

Pablo Santa Cruz 给出的答案是正确的；但是，如果有人偶然发现此页面并需要更多说明，这里有详细的细分。

示例表

假设我们有下表：

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

内连接

内部连接，如下所示：

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

只会让我们看到两个表中都出现的记录，如下所示：

1 Tim  1 Tim

内部联接没有方向（例如左或右），因为它们是明确双向的 - 我们需要两侧都匹配。

外连接

另一方面，外连接用于查找在另一个表中可能不匹配的记录。因此，您必须指定允许连接的哪一侧有缺失记录。

LEFT JOIN和RIGHT JOIN是LEFT OUTER JOIN和RIGHT OUTER JOIN的简写；我将在下面使用他们的全名来强化外连接与内连接的概念。

左外连接

左外连接，如下所示：

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...将从左表中获取所有记录，无论它们在右表中是否有匹配项，如下所示：

1 Tim   1    Tim
2 Marta NULL NULL

右外连接

右外连接，如下所示：

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...将从右表中获取所有记录，无论它们在左表中是否有匹配项，如下所示：

1    Tim   1  Tim
NULL NULL  3  Katarina

完全外连接

完整的外连接将为我们提供两个表中的所有记录，无论它们在另一个表中是否有匹配项，如果不匹配，则两侧都为 NULL。结果如下所示：

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

然而，正如 Pablo Santa Cruz 指出的那样，MySQL 不支持这一点。我们可以通过左连接和右连接的 UNION 来模拟它，如下所示：

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

您可以将 UNION 视为“运行这两个查询，然后将结果堆叠在一起”；一些行将来自第一个查询，一些来自第二个查询。

需要注意的是，MySQL 中的 UNION 会消除精确的重复项：Tim 会出现在此处的两个查询中，但 UNION 的结果仅列出他一次。我的数据库专家同事认为不应依赖这种行为。因此，为了更明确地说明这一点，我们可以向第二个查询添加一个 WHERE 子句：

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

另一方面，如果您出于某种原因想要查看重复项，则可以使用UNION ALL。

Answer 2

您在 MySQL 中没有完全连接，但您可以确定模拟它们。

对于从此堆栈溢出问题转录的代码示例 你有：

有两个表 t1、t2：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

---

上面的查询适用于完全外连接操作不会产生任何重复行的特殊情况。上面的查询依赖于 UNION 集合运算符来删除查询模式引入的重复行。我们可以通过对第二个查询使用反连接模式来避免引入重复行，然后使用 UNION ALL 集合运算符来组合两个集合。在更一般的情况下，完整的外部联接将返回重复的行，我们可以这样做：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL