如何在MySQL中进行完全外部连接？

MySQL中没有完整的连接，但您可以肯定地模拟它们。

对于从此 Stack Overflow 问题转录的代码示例，您有：

使用两个表 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

---

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

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

巴勃罗·圣克鲁斯给出的答案是正确的;但是，如果有人偶然发现此页面并希望获得更多澄清，这里有详细的细分。

示例表

假设我们有以下表格：

-- 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 JOINand 是 和 的简写;我将在下面使用它们的全名来强化外部连接与内部连接的概念。RIGHT JOINLEFT OUTER JOINRIGHT 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`;

你可以把 a 看作是“运行这两个查询，然后将结果堆叠在一起”;有些行来自第一个查询，有些来自第二个查询。UNION

应该注意的是，MySQL中的a将消除完全重复的：Tim将出现在这里的两个查询中，但结果只列出了他一次。我的数据库专家同事认为这种行为不应该被依赖。因此，为了更明确地说明这一点，我们可以在第二个查询中添加一个子句：UNIONUNIONWHERE

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 lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id

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

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

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

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

这是完全外部联接的预期结果：

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 小提琴

我建议的查询是：

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 小提琴

---

@Steve 钱伯斯：^{[来自评论，非常感谢！}

注意：这可能是最好的解决方案，无论是提高效率还是生成与 .这篇博文也很好地解释了这一点 - 引用方法 2：“这正确地处理了重复的行，并且不包含任何不应该包含的内容。有必要使用 UNION ALL 而不是普通的 UNION，这将消除我想保留的重复项。这在大型结果集上可能效率更高，因为无需对重复项进行排序和删除。FULL OUTER JOIN

---

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

完全外部连接意味着：全部在或在：所有在两者中，加上所有不在中和加所有在不在：(t1 ∪ t2)t1t2(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_onlyt1t2t1t2t2t1

-- (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 小提琴

为了更清楚起见，我修改了 shA.t 的查询：

-- t1 left join t2
SELECT t1.value, t2.value
FROM 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.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE 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 duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

如果需要它来处理值（也可能是必要的），请使用 -safe 比较运算符，而不是 .NULLNULL<=>=

用：

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

可以按如下方式重新创建它：

 SELECT t1.*, t2.*
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT 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  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

UNION解决方案：

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

给出错误的答案：

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

UNION ALL解决方案：

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

也是不正确的。

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

而这个查询：

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

给出以下内容：

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

顺序不同，但在其他方面与正确答案匹配。

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

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

使用交叉联接解决方案：

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

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

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

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

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

现在，按原样编写原始查询并没有什么坏处，但是假设如果您有像 WHERE 子句这样的谓词，它是 before-join 谓词，或者是子句上的 AND 谓词，这是一个 during-join 谓词，那么你可能想看看魔鬼;这是细节。ON

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

现在，如果您已经完成了 RIGHT JOIN，但对 t1 中的列使用了 WHERE 谓词，则可能会遇到 null 拒绝的情况。

例如，查询

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

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

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

因此，表的顺序已更改，但谓词仍应用于 t1，但 t1 现在位于“ON”子句中。如果将 t1.col1 定义为列，则此查询将被拒绝为 null。NOT NULL

任何被 null 拒绝的外部连接（左、右、全）都会被 MySQL 转换为内部连接。

因此，您可能期望的结果可能与MySQL返回的结果完全不同。您可能认为这是MySQL的RIGHT JOIN的错误，但这是不对的。这就是MySQL查询优化器的工作方式。因此，负责的开发人员在构建查询时必须注意这些细微差别。

您可以执行以下操作：

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

SQL标准说是行不匹配，左边表行由空值扩展，右表行由空值扩展。即行在但不是行中但不是行。full join oninner join onunion allunion allinner join onunion allleft join oninner join onunion allright join oninner join on

即行不在 .或者，如果您知道您的结果在特定的右表列中不能有 null，那么“不在”中的行是具有该列扩展条件的行。left join onunion allright join oninner join oninner join onright join oninner join onright join ononandis null

即同样合适的行。right join onunion allleft join on

从“INNER JOIN”和“OUTER JOIN”有什么区别？

（SQL Standard 2006 SQL/Foundation 7.7 语法规则 1、一般规则 1 b、3 c 和 d、5 b.）

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

SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition

到：

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)

或者，如果您至少有一列，比如 ，其中不是 NULL，您可以执行以下操作：foofirsttable

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL