我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?
当前回答
SQL标准表示,上的完全联接是行上的内部联接,所有不匹配的左表行由null扩展,所有右表行由nulls扩展。也就是说,行上的内部联接将左联接中的所有行联合在一起,而不是联合在一起的内部联接。
也就是说,行上的左联接和不在上的内联接中的行上的所有右联接。或者,如果您知道结果的内联接在某个特定的右表列中不能为空,那么“行上的右联接不在上内联接中”是右联接中的列,条件扩展为,并且该列为空。
也就是说,在并集上进行右连接,在行上进行所有适当的左连接。
来自“INNER JOIN”和“OUTER JOIN”之间的区别是什么?:
(SQL标准2006 SQL/Foundation 7.7语法规则1,通用规则1 b,3 c&d,5 b)
其他回答
为了更清晰,我修改了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
我修复了响应,工作包括所有行(基于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 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
使用联合查询将删除重复项,这与从不删除任何重复项的完全外部联接的行为不同:
[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
这也是可能的,但您必须在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