Does Full Outer Join in MySQL return rows that both have nulls and match on either side (using Union All in mysql)

Question

Ususal Full Join:

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

Now since UNION ALL and UNION is an implementation of FULL JOIN in MySQL, which is the result that FULL JOIN normally returns when used in databases that does support FULL JOIN, does it return a result that keeps duplicates like UNION ALL or a normal and more obvious one in UNION ?

For a detailed overview of the question keep on reading below, I've asked the question again at the end

MySQL implementation:

Joins to be done on these two tables

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   |
+--------+-------+

PURE LEFT 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    |
+-------+--------+-------+

PURE RIGHT 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  |
+-------+--------+-------+

Now since UNION ALL and UNION is an implementation of FULL JOIN in MySQL, which is the result that FULL JOIN normally returns when used in databases that does support FULL JOIN, does it return a result that keeps duplicates like UNION ALL or a normal and more obvious one in UNION ?

Thanks...

Answer 1

There exists one more FULL JOIN implementation:

SELECT t1.empno, t1.ename, t2.dname
FROM (
    SELECT deptno FROM emps
    UNION 
    SELECT deptno FROM depts
    ) t0
LEFT JOIN emps t1 USING (deptno)
LEFT JOIN depts t2 USING (deptno)