如何在 MySQL 中进行 FULL OUTER JOIN？

我想在 MySQL 中进行完全外部联接。这可能吗？ MySQL 支持完全外部联接吗？

答案

您在 MySQL 上没有 FULL JOINS，但是可以肯定地模拟它们。

对于从该 SO 问题记录下来的代码 SAMPLE，您可以：

有两个表 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

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

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

巴勃罗 · 圣克鲁斯（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理解为 “运行这两个查询，然后将结果彼此堆叠”；一些行将来自第一个查询，而某些行将来自第二个查询。

应当指出， 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 。

使用union查询将删除重复项，这与从不删除任何重复项full outer join

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

full outer join的预期结果：

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

这是使用的结果， left和right Join与union ：

value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

我建议的查询是：

select 
    t1.value, t2.value
from t1 
left outer join t2  
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select 
    t1.value, t2.value
from t2 
left outer join t1 
  on t1.value = t2.value
where 
    t1.value IS NULL

上述查询的结果与预期结果相同：

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]

@史蒂夫 · 钱伯斯（Steve Chambers）： ^{[评论，非常感谢！]}
注意：这可能是最好的解决方案，从效率和产生与FULL OUTER JOIN相同的结果来看都是如此。这篇博客文章也很好地解释了这一点 - 引用方法 2： “此方法可以正确处理重复的行，并且不包含不应包含的任何内容。有必要使用UNION ALL而不是普通的UNION ，这样可以消除我想要的重复项保留。对于大型结果集，这可能会显着提高效率，因为无需排序和删除重复项。”

我决定添加另一种解决方案，该解决方案来自full outer join可视化和数学计算，这不是上面的更好，但更具可读性：

完全外部联接方式(t1 ∪ t2) ：全部在t1或t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only ： t1和t2所有内容加上t1中不在t2所有内容以及t2中不在t1 ：

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value    
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)    
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]