MySQL中FULL OUTER JOIN等效实现：UNION与UNION ALL结果差异问询

MySQL本身不支持FULL OUTER JOIN语法，通常需要通过LEFT OUTER JOIN与RIGHT OUTER JOIN结合UNION或UNION ALL来模拟全外连接。但两种模拟方式结果不同：UNION会自动去重，UNION ALL则保留所有重复行。现在需要明确：在原生支持FULL OUTER JOIN的数据库中，原生全外连接的返回结果是类似UNION的去重形式，还是类似UNION ALL的保留重复形式？

数据表及查询示例 #

常规全外连接语法 #

SELECT empno, ename, dname 
FROM emps
FULL OUTER JOIN depts
ON emps.deptno = depts.deptno;

数据表结构 #

select * from emps;的查询结果：

+-------+--------+--------+------+
| empno | ename  | deptno | mgr  |
+-------+--------+--------+------+
|     1 | Amit   |     10 |    4 |
|     2 | Rahul  |     10 |    3 |
|     3 | Nilesh |     20 |    4 |
|     4 | Nitin  |     50 |    5 |
|     5 | Sarang |     50 | NULL |
+-------+--------+--------+------+

select * from depts;的查询结果：

+--------+-------+
| deptno | dname |
+--------+-------+
|     10 | DEV   |
|     20 | QA    |
|     30 | OPS   |
|     40 | ACC   |
+--------+-------+

各类查询结果 #

纯RIGHT JOIN结果

查询语句：

SELECT empno, ename, dname from emps RIGHT OUTER JOIN depts ON emps.deptno = depts.deptno
ORDER BY empno;

结果：

+-------+--------+-------+
| empno | ename  | dname |
+-------+--------+-------+
|  NULL | NULL   | OPS   |
|  NULL | NULL   | ACC   |
|     1 | Amit   | DEV   |
|     2 | Rahul  | DEV   |
|     3 | Nilesh | QA    |
+-------+--------+-------+

纯LEFT JOIN结果

查询语句：

SELECT empno, ename, dname from emps LEFT OUTER JOIN depts ON emps.deptno = depts.deptno;

结果：

+-------+--------+-------+
| empno | ename  | dname |
+-------+--------+-------+
|     1 | Amit   | DEV   |
|     2 | Rahul  | DEV   |
|     3 | Nilesh | QA    |
|     4 | Nitin  | NULL  |
|     5 | Sarang | NULL  |
+-------+--------+-------+

UNION组合结果

查询语句：

SELECT empno, ename, dname from emps RIGHT OUTER JOIN depts ON emps.deptno = depts.deptno
UNION
SELECT empno, ename, dname from emps LEFT OUTER JOIN depts ON emps.deptno = depts.deptno
ORDER BY empno;

结果：

+-------+--------+-------+
| empno | ename  | dname |
+-------+--------+-------+
|  NULL | NULL   | OPS   |
|  NULL | NULL   | ACC   |
|     1 | Amit   | DEV   |
|     2 | Rahul  | DEV   |
|     3 | Nilesh | QA    |
|     4 | Nitin  | NULL  |
|     5 | Sarang | NULL  |
+-------+--------+-------+

UNION ALL组合结果

查询语句：

SELECT empno, ename, dname from emps RIGHT OUTER JOIN depts ON emps.deptno = depts.deptno
UNION ALL
SELECT empno, ename, dname from emps LEFT OUTER JOIN depts ON emps.deptno = depts.deptno
ORDER BY empno;

结果：

+-------+--------+-------+
| empno | ename  | dname |
+-------+--------+-------+
|  NULL | NULL   | OPS   |
|  NULL | NULL   | ACC   |
|     1 | Amit   | DEV   |
|     1 | Amit   | DEV   |
|     2 | Rahul  | DEV   |
|     2 | Rahul  | DEV   |
|     3 | Nilesh | QA    |
|     3 | Nilesh | QA    |
|     4 | Nitin  | NULL  |
|     5 | Sarang | NULL  |
+-------+--------+-------+

答案 #

原生FULL OUTER JOIN的返回结果和UNION的去重形式一致，不会保留重复行。

原因在于，全外连接的逻辑是：返回左表所有行、右表所有行，以及两张表中满足连接条件的匹配行，但对于同时存在于左连接和右连接结果中的匹配行，原生全外连接只会返回一次，不会重复输出。

对应你提供的示例，原生FULL OUTER JOIN的结果会和UNION组合后的结果完全相同，不会出现UNION ALL里的重复行。

内容的提问来源于stack exchange，提问作者learningForever