Full Outer Join get repeated with Union?

Question

I'm trying to accomplish a Full Outer Join with my SQL.

Reference Link

• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Although apparently this is not supported. I've looked around and have come across this accepted answer: https://stackoverflow.com/a/4796911/3859456

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

Although won't this at least repeat the matched records twice when we do a Union? If not does a union automatically overwrite the matched records to the 2 tables?

E.g.

• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table

• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table

Union Left-Outer-Table + (left-matched = right-matched)x2 + Right-Outer-Table

I'm sure the answer works as the community trust it. But I'm still confused as to how it works and hope that someone can help me understand better.

Answer 1

To reiterate from the accepted answer to which you refer, I will quote both the UNION and UNION ALL versions:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

and

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

If there were no duplicates generated by the join, then these two queries would return the same result set. The reason can be explained as:

• The first half of the UNION/UNION ALL returns all records in common between the two tables (no duplicates, by our assumption), and it also return those records unique to the first table t1.
• The second half of the union query returns all records in common and all records unique to the second table t2. But the UNION filters out those duplicate common records without altering the result set, since we assumed there are no duplicates.
• The second half of the union all query selectively removes the duplicate common records using WHERE t1.id IS NULL. This ensures that only the records unique to the second table are added by the second half of the UNION ALL.

Now, if the first table itself happened to have duplicates, this is what would happen:

• In the union query, duplicate records which occurred in the first table would be filtered off. This is subtle, because duplicates can arise from two sources here. First, there could be duplicates with the first table itself. Second, there could be duplicates which arise from the join. All duplicates would be removed from a UNION.
• However, in the union all query, no duplicates would be removed. The duplicate records which might happen to appear in the first table would survive intact in the final result set, as would any duplicates which resulted from the join.

This is a long winded answer, but hopefully it convinces you that in the case of duplicates, the UNION and UNION ALL versions of the accepted answer may not generate the same result set.