我想在MySQL中进行完全的外部连接。这可能吗？MySQL是否支持完全外部联接？
当前回答
使用联合查询将删除重复项，这与从不删除任何重复项的完全外部联接的行为不同：
[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
@史蒂夫·钱伯斯：（来自评论，非常感谢！）
注意：这可能是最佳的解决方案，既可以提高效率，也可以生成与FULL OUTER JOIN相同的结果。这篇博客文章也很好地解释了这一点，引用了方法2的话：“这可以正确处理重复的行，并且不包含任何不应该包含的内容。有必要使用UNION ALL而不是普通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
其他回答
您在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
您可以只转换一个完整的外部联接，例如。
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
巴勃罗·圣克鲁斯给出的答案是正确的；然而，如果有人无意中看到了这一页，并希望得到更多的澄清，这里有一个详细的分类。
示例表
假设我们有以下表格：
-- 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 JON的简写；我将在下面使用它们的全名来强化外部连接与内部连接的概念。
左侧外部连接
左外连接，如下所示：
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。
SQL标准表示，上的完全联接是行上的内部联接，所有不匹配的左表行由null扩展，所有右表行由nulls扩展。也就是说，行上的内部联接将左联接中的所有行联合在一起，而不是联合在一起的内部联接。
也就是说，行上的左联接和不在上的内联接中的行上的所有右联接。或者，如果您知道结果的内联接在某个特定的右表列中不能为空，那么“行上的右联接不在上内联接中”是右联接中的列，条件扩展为，并且该列为空。
也就是说，在并集上进行右连接，在行上进行所有适当的左连接。
来自“INNER JOIN”和“OUTER JOIN”之间的区别是什么？：
（SQL标准2006 SQL/Foundation 7.7语法规则1，通用规则1 b，3 c&d，5 b）
使用联合查询将删除重复项，这与从不删除任何重复项的完全外部联接的行为不同：
[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
@史蒂夫·钱伯斯：（来自评论，非常感谢！）
注意：这可能是最佳的解决方案，既可以提高效率，也可以生成与FULL OUTER JOIN相同的结果。这篇博客文章也很好地解释了这一点，引用了方法2的话：“这可以正确处理重复的行，并且不包含任何不应该包含的内容。有必要使用UNION ALL而不是普通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
推荐文章
- 如何使用SQL Server 2008做多个CASE WHEN条件?
- 计数在VARCHAR字段中字符串的出现次数?
- 如何选择多行填充常量?
- 修改一个MySQL列为AUTO_INCREMENT
- 如果没有使用EXISTS引入子查询，则只能在选择列表中指定一个表达式
- SQL Server -事务回滚错误?
- 如何在SQL中有效地计数列值的发生?
- PostgreSQL列名区分大小写吗?
- 在WHERE子句中引用列别名
- 在ROR迁移期间，将列类型从Date更改为DateTime
- 恢复未保存的SQL查询脚本
- 如何删除所有MySQL表从命令行没有DROP数据库权限?
- 从主机连接到docker容器中的mysql
- 如果任何字段包含NULL, MySQL CONCAT将返回NULL
- 向临时表中插入数据
