我想在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
其他回答
在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
巴勃罗·圣克鲁斯给出的答案是正确的；然而，如果有人无意中看到了这一页，并希望得到更多的澄清，这里有一个详细的分类。
示例表
假设我们有以下表格：
-- 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
为了更清晰，我修改了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
推荐文章
- 选项(RECOMPILE)总是更快;为什么?
- 设置数据库从单用户模式到多用户
- oracle中的RANK()和DENSE_RANK()函数有什么区别?
- 警告用户/local/mysql/data目录不属于mysql用户
- 的类型不能用作索引中的键列
- SQL逻辑运算符优先级:And和Or
- 如何检查一个表是否存在于给定的模式中
- 添加一个复合主键
- 如何在SQL Server Management Studio中查看查询历史
- 无法添加或更新子行:外键约束失败
- 如何从本地机器mysqldump远程数据库
- 可以为公共表表达式创建嵌套WITH子句吗?
- 什么时候我需要在Oracle SQL中使用分号vs斜杠?
- SQL Server的NOW()?
- 在SQL中，count(列)和count(*)之间的区别是什么?
