在MySQL中模拟完全外部联接的有效方法? [英] Efficient way to simulate full outer join in MySQL?

问题描述

根据Google搜索:由于MySQL不支持完全外部联接，因此可以通过union和/或union all模拟它.但是，这两种方法都会删除真实的重复项或显示伪造的重复项.

According to Google search: since MySQL does not support full outer join, it could be simulated via union and/or union all. But both of these either remove genuine duplicates or show spurious duplicates.

什么是正确有效的方式?

What would be correct and efficient way?

此问题似乎相关，但无法得到答案.

This question seems relevant but couldn't get the answer of it.

推荐答案

您可以使用LEFT JOIN和RIGHT JOIN:

You can use a LEFT JOIN and a RIGHT JOIN:

SELECT * FROM tableA LEFT JOIN tableB ON tableA.b_id = tableB.id
UNION ALL
SELECT * FROM tableA RIGHT JOIN tableB ON tableA.b_id = tableB.id
WHERE tableA.b_id IS NULL

在Wikipedia上也有关于此主题的一些信息:完全外部联接.

There is also some information on Wikipedia about this topic: Full outer join.

维基百科的文章建议在MySQL中使用UNION.这比UNION ALL慢一点，但更重要的是，它不会总是给出正确的结果-它将从输出中删除重复的行.因此，更喜欢在这里使用UNION ALL而不是UNION.

The Wikipedia article suggests using a UNION in MySQL. This is slightly slower than UNION ALL, but more importantly it won't always give the correct result - it will remove duplicated rows from the output. So prefer to use UNION ALL instead of UNION here.

这篇关于在MySQL中模拟完全外部联接的有效方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！