What is a FULL OUTER JOIN and how can you simulate it in MySQL?
FULL OUTER JOIN returns all rows from both tables, matching where possible and filling NULLs where not. MySQL has no FULL JOIN, so combine a LEFT JOIN and a RIGHT JOIN with UNION.
Explain it simply
Two lists: students who registered for a fest, and students who paid. A FULL OUTER JOIN gives everybody from either list. Registered and paid appear once, registered but unpaid have NULL on the payment side, and paid but not registered have NULL on the registration side.
Example
-- PostgreSQL, SQL Server, Oracle
SELECT r.student, p.amount
FROM registrations r
FULL OUTER JOIN payments p ON r.student = p.student;MySQL workaround:
SELECT r.student, p.amount
FROM registrations r
LEFT JOIN payments p ON r.student = p.student
UNION
SELECT r.student, p.amount
FROM registrations r
RIGHT JOIN payments p ON r.student = p.student;UNION removes the duplicate matched rows that both halves produce. Be careful: if genuine duplicate rows exist in your data, UNION would also merge those.
To find only the mismatches, add WHERE r.student IS NULL OR p.student IS NULL to the full join.
What to say in the interview
"FULL OUTER JOIN is a LEFT plus RIGHT join without duplicating matches. It is handy for reconciling two sources. MySQL lacks it, so I use LEFT JOIN UNION RIGHT JOIN."
Common mistakes
- Using UNION ALL in the workaround, which duplicates every matched row.