FULL JOIN combines LEFT and RIGHT JOIN's behavior at once: every row from both tables appears, matched where the join condition finds a counterpart, NULL on whichever side didn't match. It's the least commonly needed of the four, but it's the only one that shows unmatched rows from both directions in a single result.
Learning Objectives
- Write a FULL JOIN and predict which rows appear with NULLs on which side.
- Know which databases in this course don't support FULL JOIN directly.
- Simulate FULL JOIN using LEFT JOIN UNION RIGHT JOIN where it isn't supported.
employees
Unmatched rows: NULL department
FULL JOIN
Keeps unmatched rows from both sides
departments
Unmatched rows: Legal, NULL employee
Result
Nothing from either table is dropped
Basic FULL JOIN
Every employee appears, even one whose department_id didn't match anything, and every department appears, including Legal, with NULL filled in on whichever side has no counterpart. INNER JOIN would show neither of these edge cases, LEFT or RIGHT JOIN alone would only show one.
SELECT e.first_name, d.name AS department_name FROM employees e FULL JOIN departments d ON e.department_id = d.id;
MySQL and MariaDB Don't Support FULL JOIN
| Database | FULL JOIN Support |
|---|---|
| PostgreSQL / SQL Server / Oracle | Supported |
| SQLite | Supported from version 3.39 (2022) onward |
| MySQL / MariaDB | Not supported, simulate with UNION instead |
Simulating FULL JOIN With UNION
LEFT JOIN captures every employee (matched or not), RIGHT JOIN captures every department (matched or not), and UNION combines the two results into one, automatically removing the rows that appear in both (every row that actually matched on both sides). UNION itself, including exactly how it removes duplicates, gets a full lesson in the next module, this is simply its most common real use.
SELECT e.first_name, d.name AS department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id UNION SELECT e.first_name, d.name AS department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;
Try It Yourself
Write a query that returns every employee/department pairing with nothing excluded on either side, for a database that doesn't support FULL JOIN.
SELECT e.first_name, d.name AS department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id UNION SELECT e.first_name, d.name AS department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;
Frequently Asked Questions
Is FULL JOIN the same as FULL OUTER JOIN?
Yes, OUTER is optional here too, FULL JOIN and FULL OUTER JOIN mean the same thing in every database that supports it.
How common is FULL JOIN in real queries?
Genuinely rare compared to INNER and LEFT JOIN, most real questions only need "all of A" or "only matches," not "everything from both sides, matched or not." It shows up most often in data-reconciliation style tasks, comparing two datasets to find what's missing from each.
Common Beginner Mistakes
Using FULL JOIN on MySQL and getting a syntax error
MySQL and MariaDB never implemented FULL JOIN, it's not a version issue, it simply isn't there. The LEFT JOIN UNION RIGHT JOIN pattern is the standard workaround.
Using UNION ALL instead of UNION in the simulated version
UNION ALL would keep the matched rows twice, once from the LEFT JOIN half and once from the RIGHT JOIN half, since it doesn't deduplicate. Plain UNION is required here specifically so the rows both halves agree on only appear once.
Best Practices
- Reach for FULL JOIN only when unmatched rows from both sides genuinely matter, it's rarely the right default.
- On MySQL/MariaDB, use LEFT JOIN UNION RIGHT JOIN (not UNION ALL) as the standard substitute.
- Check a specific database's FULL JOIN support before relying on it in a portable query.
Interview Questions
How would you write a FULL JOIN on a database that doesn't support it directly?
Combine a LEFT JOIN (every row from the first table, matched or not) and a RIGHT JOIN (every row from the second table, matched or not) with UNION, not UNION ALL, so rows that matched on both sides, which appear in both halves, aren't duplicated.
What's the practical difference between FULL JOIN and doing a LEFT JOIN and a RIGHT JOIN separately?
FULL JOIN returns one combined result set containing both outcomes at once. Running LEFT JOIN and RIGHT JOIN separately produces two separate result sets that would need to be manually combined, which is exactly what the UNION-based simulation does under the hood.
Summary
FULL JOIN keeps every row from both tables, matched where possible, NULL on whichever side has no counterpart. PostgreSQL, SQL Server, Oracle, and modern SQLite support it directly; MySQL and MariaDB need the LEFT JOIN UNION RIGHT JOIN workaround instead.
What's Next?
SELF JOIN closes out this module, a table joined to itself, used here to pair every employee with their manager through the manager_id column.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.