FULL JOIN: Keeping Unmatched Rows From Both Sides

Overview

A FULL JOIN (formally FULL OUTER JOIN) returns all rows from both tables, filling in NULL wherever there's no match on the other side. It's the union of a LEFT JOIN and a RIGHT JOIN. This is the query you reach for when auditing data integrity, finding orphaned records, departments without staff, or employees without a valid department.

The Query

Step-by-Step Breakdown

1. FROM employees e FULL OUTER JOIN departments d: keeps every employee and every department.
2. ON e.department_id = d.id: the match condition; unmatched rows get NULL for the opposite side's columns.
3. In this clean dataset every employee has a department and every department has staff, so no NULL gaps appear, but the structure is what makes integrity checks possible.

Variations

Find employees or departments with no counterpart:

Common Mistakes

• Forgetting OUTER. In standard SQL the keyword is FULL OUTER JOIN; some engines accept FULL JOIN as shorthand (DuckDB does).
• Assuming all rows match. A FULL JOIN deliberately keeps unmatched rows, so expect NULLs and handle them with COALESCE if you need a placeholder.
• Using it when you only need one side. If you only care about keeping the left table, a LEFT JOIN is simpler and clearer.