如何在 MySQL 中进行完全外部联接? [英] How can I do a FULL OUTER JOIN in MySQL?

问题描述

我想做一个全外连接 在 MySQL 中.这可能吗?MySQL 是否支持全外连接?

推荐答案

你在 MySQL 中没有 full joins，但你可以确定 模仿他们.

You don't have full joins in MySQL, but you can sure emulate them.

对于从这个堆栈溢出问题转录的代码示例 你有:

For a code sample transcribed from this Stack Overflow question you have:

有两个表 t1、t2:

With two tables 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 集合运算符来删除查询模式引入的重复行.我们可以通过对第二个查询使用 anti-join 模式来避免引入重复行，然后使用 UNION ALL 集合运算符将两个集合组合起来.在更一般的情况下，全外连接会返回重复的行，我们可以这样做:

---

The query above works for special cases where a full outer join operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a full outer join would return duplicate rows, we can do this:

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

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