Outer Join

Definition

An Outer Join extends a regular (inner) join by also including UNMATCHED rows from one or both sides, filling in NULL for the columns that have no match. There are three variants: Left Outer Join (keeps all rows from the left table), Right Outer Join (keeps all rows from the right table), and Full Outer Join (keeps all rows from both).

How It Works

sql
SELECT e.name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

A plain (inner) JOIN here would silently DROP any employee whose department_id doesn't match a real department (or is NULL) — those employees simply wouldn't appear in the result at all. LEFT JOIN instead keeps every employee row regardless, showing NULL for department_name when there's no match. This is exactly the anti-join pattern used elsewhere in this course (Referential Integrity, 5.11) to FIND those very rows: ... LEFT JOIN ... WHERE d.id IS NULL.

Edge Cases and Pitfalls

• LEFT JOIN and RIGHT JOIN are mirror images of each other — A LEFT JOIN B is equivalent to B RIGHT JOIN A with the column order swapped; most people standardize on always writing LEFT JOIN for consistency rather than mixing both directions in the same codebase.
• Placing a filter on the RIGHT-hand (outer) table's column in the WHERE clause instead of the ON clause can silently turn a LEFT JOIN back into behaving like an inner join — e.g. LEFT JOIN departments d ON ... WHERE d.status = 'active' discards the very NULL-department rows the LEFT JOIN was meant to preserve, because WHERE runs after the join and NULL = 'active'-style comparisons are never true. The filter belongs in the ON clause instead if you want to keep those rows.
• FULL OUTER JOIN isn't supported by every dialect (notably, older MySQL versions lack it directly) — it can be simulated with a LEFT JOIN UNION a RIGHT JOIN, combining both directions.

Key Takeaways

• Outer Join keeps unmatched rows (filling NULLs) instead of dropping them like an inner join does; LEFT/RIGHT/FULL specify which side(s) are preserved.
• A WHERE-clause filter on the "kept-NULL" side can accidentally undo a LEFT JOIN's whole purpose — such filters usually belong in the ON clause instead.
• FULL OUTER JOIN isn't universally supported and can be simulated with LEFT JOIN UNION RIGHT JOIN where it's missing.