How Can I Do a Full Outer Join in Mysql?
I Want to Do a Full Outer Join in Mysql. Is This Possible? Is a Full Outer Join Supported by Mysql? 6 15 Answers You Don't Have Full Joins in Mysql, but You...
I want to do a full outer join in MySQL. Is this possible? Is a full outer join supported by MySQL?
15 Answers
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:
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
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
The answer that Pablo Santa Cruz gave is correct; however, in case anybody stumbled on this page and wants more clarification, here is a detailed breakdown.
Must Read
Example Tables
Suppose we have the following tables:
-- t1
id name
1 Tim
2 Marta
-- t2
id name
1 Tim
3 Katarina
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
