如何在 MySQL 中执行 FULL OUTER JOIN？

Question

我想要执行完全外部联接 MySQL。这可能吗？ MySQL 支持全外连接吗？

Answer 1

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

对于从此堆栈溢出问题示例 a> 您有：

对于两个表 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

Answer 2

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

右外连接

右外连接，如this:

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 3

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

[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

这是使用左和右连接与联合的结果：

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：^{[来自评论，非常感谢！]}< /p>

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

---

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

完全外连接意味着(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

Answer 4

前面的答案实际上都不正确，因为当存在重复值时它们不遵循语义。

对于诸如（来自 此重复）的查询：

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;

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

Answer 5

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的外连接简化，正确的通过在查询中的 FROM 和 ON 子句中切换 t1 和 t2，join 将转换为等效的左联接。因此，MySQL 查询优化器将原始查询转换为以下内容 -

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 谓词ON 子句，这是一个 during-join谓词，那么你可能想看看魔鬼；这是详细信息。

MySQL 查询优化器会例行检查谓词是否拒绝 null。

现在，如果您已完成 RIGHT JOIN，但在 t1 的列上使用 WHERE 谓词，那么您可能面临null-rejected的风险设想。

例如，

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 定义为 NOT NULL
列，那么该查询将被拒绝 null。

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

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

Answer 6

我修改了 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 

Answer 7

在 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

Answer 8

您可以将完整的外部联接转换

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)

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

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

Answer 9

您可以执行以下操作：

(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);

Answer 10

表 L 和 R 之间的完全外连接包括：

1. 来自 L x R 的行，其中谓词匹配，如 l, r
2. L 中剩余的行（如果有），如 l, r
3. R 中的剩余行（如果有），如 l, r< col1, col2, ...>

要模拟此行为：

• 可以使用 左连接 组合设置 #1 和 #2 来实现
• 设置 #3 可以使用 来实现不存在
• 使用union all合并两个集合

查询

select l.*, r.*
from employee as l
left join department as r on l.fk = r.id

union all

select null, null, null, r.*
from department as r
where not exists (
  select 1
  from employee as l
  where l.fk = r.id
)

示例数据和结果

create table department (
  id varchar(100) not null primary key,
  name varchar(100)
)
insert into department (id, name) values
('d1', 'programming'),
('d2', 'designing'),
('d3', 'accounting')

create table employee (
  id varchar(100) not null primary key,
  name varchar(100),
  fk varchar(100)
)
insert into employee (id, name, fk) values
('e1', 'john', 'd1'),
('e2', 'jane', 'd2'),
('e4', 'zack', null)

| id   | name | fk   | id   | name        |
|------|------|------|------|-------------|
| e1   | john | d1   | d1   | programming |
| e2   | jane | d2   | d2   | designing   |
| e4   | zack | null | null | null        |
| null | null | null | d3   | accounting  |

DB<>小提琴

Answer 11

您可以使用 union all 而不是完全联接。我会做这样的事情：

select
    name, sum(num1) num1, sum(num2) num2
from
(
select name, num1, 0 num2 from table1
union all
select name, 0 num1, num2 from table2
) t
group by name

在这里，“名称”列相当于要加入的列。
这样做的好处是不需要加入两次，只需要分组一次。

Answer 12

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

Answer 13

使用：

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

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

Answer 14

也是可以的，但是你必须在 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