如何在MySQL中执行FULL OUTER JOIN？

你在MySQL中没有全面加入，但你可以确定效仿他们。

对于从这个堆栈溢出问题转录的代码样本，您有：

有两个表t1、t2：

SELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idUNIONSELECT * FROM t1RIGHT JOIN t2 ON t1.id = t2.id

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

SELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idUNION ALLSELECT * FROM t1RIGHT JOIN t2 ON t1.id = t2.idWHERE t1.id IS NULL

巴勃罗圣克鲁斯给出的答案是正确的；然而，如果有人偶然发现这个页面并想要更多的澄清，这里有一个详细的细分。

示例表

假设我们有以下表格：

-- t1id  name1   Tim2   Marta
-- t2id  name1   Tim3   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    Tim2 Marta NULL NULL

右外连接

右向外连接，像这样：

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

…会从右表中获取所有记录，而不管它们在左表中是否匹配，如下所示：

1    Tim   1  TimNULL NULL  3  Katarina

完全外连接

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

1    Tim   1    Tim2    Marta NULL NULLNULL 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。

在SQLite中，你应该这样做：

SELECT *FROM leftTable ltLEFT JOIN rightTable rt ON lt.id = rt.lridUNIONSELECT lt.*, rl.*  -- To match column setFROM rightTable rtLEFT JOIN  leftTable lt ON lt.id = rt.lrid

SELECTa.name,b.titleFROMauthor AS aLEFT JOINbook AS bON a.id = b.author_idUNIONSELECTa.name,b.titleFROMauthor AS aRIGHT JOINbook AS bON a.id = b.author_id

这也是可能的，但您必须在选择中提及相同的字段名称。

SELECT t1.name, t2.name FROM t1LEFT JOIN t2 ON t1.id = t2.idUNIONSELECT t1.name, t2.name FROM t2LEFT JOIN t1 ON t1.id = t2.id

使用联盟查询将删除重复项，这与全外连接从不删除任何重复项的行为不同：

[Table: t1]        [Table: t2]value              value-----------        -------1                  12                  24                  24                  5

这是全外连接的预期结果：

value | value------+-------1     | 12     | 22     | 2Null  | 54     | Null4     | Null

这是将左和右加入与联盟一起使用的结果：

value | value------+-------Null  | 51     | 12     | 24     | Null

SQL小提琴

我建议的查询是：

selectt1.value, t2.valuefrom t1left outer join t2on t1.value = t2.valueunion all      -- Using `union all` instead of `union`selectt1.value, t2.valuefrom t2left outer join t1on t1.value = t2.valuewheret1.value IS NULL

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

value | value------+-------1     | 12     | 22     | 24     | NULL4     | NULLNULL  | 5

SQL小提琴

@陈志立：^{（来自评论，非常感谢！）}

备注：这可能是最好的解决方案，无论是效率还是产生与FULL OUTER JOIN相同的结果。这篇博客文章也很好地解释了这一点-引用方法2："这将正确处理重复的行，并且不包含任何不应该包含的内容。有必要使用#1而不是普通的#2，这将消除我想要保留的重复项。这在大型结果集中可能会更有效，因为不需要排序和删除重复项。"

我决定添加另一个来自全外连接可视化和数学的解决方案。它不比上面的好，但更具可读性：

完全外连接表示(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 t2select t1.value, t2.valuefrom t1 join t2 on t1.value = t2.valueunion all  -- And plus-- all in t1 that not exists in t2select t1.value, nullfrom t1where not exists( select 1 from t2 where t2.value = t1.value)union all  -- and plus-- all in t2 that not exists in t1select null, t2.valuefrom t2where not exists( select 1 from t1 where t2.value = t1.value)

SQL小提琴

我修改了sha. t的查询以更清晰：

-- t1 left join t2SELECT t1.value, t2.valueFROM t1 LEFT JOIN t2 ON t1.value = t2.value
UNION ALL -- include duplicates
-- t1 right exclude join t2 (records found only in t2)SELECT t1.value, t2.valueFROM t1 RIGHT JOIN t2 ON t1.value = t2.valueWHERE t1.value IS NULL

前面的答案实际上没有一个是正确的，因为当有重复的值时，它们不遵循语义学。

对于这样的查询（来自这个重复）：

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

正确的等价物是：

SELECT t1.*, t2.*FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicatesSELECT name FROM t2) n LEFT JOINt1ON t1.name = n.name LEFT JOINt2ON t2.name = n.name;

如果您需要使用NULL值（这可能也是必要的），请使用NULL-安全比较运算符，<=>而不是=。

用途：

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

它可以重新创建如下：

 SELECT t1.*, t2.*FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmpLEFT JOIN t1 ON t1.id = tmp.idLEFT JOIN t2 ON t2.id = tmp.id;

使用UNION或UNION ALL答案不包括基表具有重复条目的边缘情况。

说明：

有一种UNION或UNION ALL无法覆盖的边缘情况。我们不能在MySQL上测试这一点，因为它不支持完整的外连接，但我们可以在支持它的数据库上说明这一点：

 WITH cte_t1 AS(     SELECT 1 AS id1     UNION ALL SELECT 2     UNION ALL SELECT 5     UNION ALL SELECT 6     UNION ALL SELECT 6),cte_t2 AS(     SELECT 3 AS id2     UNION ALL SELECT 4     UNION ALL SELECT 5     UNION ALL SELECT 6     UNION ALL SELECT 6)SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

这给了我们这样的答案：

id1  id21  NULL2  NULLNULL  3NULL  45  56  66  66  66  6

UNION解决方案：

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2UNION    SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

给出错误答案：

 id1  id2NULL  3NULL  41  NULL2  NULL5  56  6

联合所有解决方案：

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2UNION ALLSELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

也是不正确的。

id1  id21  NULL2  NULL5  56  66  66  66  6NULL  3NULL  45  56  66  66  66  6

而这个查询：

SELECT t1.*, t2.*FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmpLEFT JOIN t1 ON t1.id = tmp.idLEFT JOIN t2 ON t2.id = tmp.id;

给出以下内容：

id1  id21  NULL2  NULLNULL  3NULL  45  56  66  66  66  6

顺序不同，但与正确答案匹配。

我修复了响应，作品包括所有行（基于Pavle Lekic的响应）：

    (SELECT a.* FROM tablea aLEFT JOIN tableb b ON a.`key` = b.keyWHERE b.`key` is null)UNION ALL(SELECT a.* FROM tablea aLEFT JOIN tableb b ON a.`key` = b.keywhere  a.`key` = b.`key`)UNION ALL(SELECT b.* FROM tablea aright JOIN tableb b ON b.`key` = a.keyWHERE a.`key` is null);

使用交叉连接解决方案：

SELECT t1.*, t2.*FROM table1 t1INNER JOIN table2 t2ON 1=1;

MySQL没有FULL-OUTER-JOIN语法。您必须通过执行LEFT JOIN和Right JOIN来模拟它，如下所示：

SELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idUNIONSELECT * FROM t1RIGHT JOIN t2 ON t1.id = t2.id

但是MySQL也没有右连接语法。根据MySQL的外连接简化，通过在查询的FROM和ON子句中切换t1和t2，右连接被转换为等效的左连接。因此，MySQL查询优化器将原始查询转换为以下内容-

SELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idUNIONSELECT * FROM t2LEFT JOIN t1 ON t2.id = t1.id

现在，按原样编写原始查询没有坏处，但是如果你有像WHERE子句这样的谓词，它是之前-加入谓词，或者ON子句上的AND谓词，它是期间-加入谓词，那么你可能想看看魔鬼；这是细节。

MySQL查询优化器会定期检查谓词是否为零拒绝。

现在，如果您已经完成了正确的JOIN，但在t1的列上使用了WHERE谓词，那么您可能会遇到零拒绝场景的风险。

例如，查询

SELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idWHERE t1.col1 = 'someValue'UNIONSELECT * FROM t1RIGHT JOIN t2 ON t1.id = t2.idWHERE t1.col1 = 'someValue'

由查询优化器转换为以下内容：

SELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idWHERE t1.col1 = 'someValue'UNIONSELECT * FROM t2LEFT JOIN t1 ON t2.id = t1.idWHERE t1.col1 = 'someValue'

所以表的顺序发生了变化，但谓词仍然应用于t1，但t1现在在'ON'子句中。如果t1.col1定义为NOT NULL列，则此查询将为零拒绝。

任何零拒绝的外连接（左、右、满）都被MySQL转换为内连接。

因此，您可能期望的结果可能与MySQL返回的结果完全不同。您可能认为这是MySQL的正确加入的bug，但事实并非如此。这只是MySQL查询优化器的工作方式。因此，负责的开发人员在构建查询时必须注意这些细微差别。

您可以执行以下操作：

(SELECT*FROMtable1 t1LEFT JOINtable2 t2 ON t1.id = t2.idWHEREt2.id IS NULL)UNION ALL(SELECT*FROMtable1 t1RIGHT JOINtable2 t2 ON t1.id = t2.idWHEREt1.id IS NULL);

SQL标准说full join on是inner join on行union all不匹配的左表行由空值扩展union all右表行由空值扩展。即inner join on行left join on中的2行，但不是right join on中的inner join onunion all行，但不是inner join on。

即left join on行union allright join on行不在inner join on中。或者，如果您知道您的inner join on结果在特定的右表列中不能为空，那么“right join on行不在inner join on中”是right join on中的行，on条件由and扩展到该列union all0。

Ie类似地right join onunion all适当的left join on行。

从“内部连接”和“外部连接”有什么区别？：

（SQL标准2006SQL /Foundation7.7语法规则1，一般规则1 b，3 c&d，5 b。）

您可以只转换一个完整的外连接，例如。

SELECT fieldsFROM firsttableFULL OUTER JOIN secondtable ON joincondition

分为：

SELECT fieldsFROM firsttableLEFT JOIN secondtable ON joinconditionUNION ALLSELECT fields (replacing any fields from firsttable with NULL)FROM secondtableWHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)

或者，如果您至少有一列，例如foo，在firsttable中不是NULL，则可以执行以下操作：

SELECT fieldsFROM firsttableLEFT JOIN secondtable ON joinconditionUNION ALLSELECT fieldsFROM firsttableRIGHT JOIN secondtable ON joinconditionWHERE firsttable.foo IS NULL