FULL OUTER JOIN

Definition

FULL OUTER JOIN keeps every row from BOTH tables, matching where possible and filling NULL on whichever side lacks a match — the union of what LEFT JOIN and RIGHT JOIN each preserve.

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

This returns every employee (including Vikram, with NULL department) AND every department (including Marketing, with NULL employee name) — nothing from either side is ever dropped.

Edge Cases and Pitfalls

• As covered in 7.11: FULL OUTER JOIN is NOT supported by every dialect — notably, MySQL has no native FULL OUTER JOIN (added only in much more recent versions in limited form) and it must be simulated with LEFT JOIN UNION RIGHT JOIN.
• The simulated version needs care: (A LEFT JOIN B) UNION (A RIGHT JOIN B) correctly captures every row from both sides, with matched rows appearing once (since UNION de-duplicates identical rows) — using UNION ALL instead would incorrectly double-count every matched row.
• FULL OUTER JOIN combined with WHERE a.id IS NULL OR b.id IS NULL is a way to find ALL unmatched rows from EITHER side in one query — a "full anti-join," useful for a two-way reconciliation check between two related tables.

Key Takeaways

• FULL OUTER JOIN preserves every row from both tables, filling NULL wherever a match is missing on either side.
• Not universally supported (MySQL notably lacks native support) — simulate with LEFT JOIN UNION RIGHT JOIN where needed.
• Combined with an IS NULL check on either side, it becomes a two-way reconciliation/anti-join across both tables.