FULL JOIN: Keeping Unmatched Rows From Both Sides
Use FULL OUTER JOIN to see every department and every employee, surfacing mismatches on either side.
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
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
FROM employees e FULL OUTER JOIN departments d— keeps every employee and every department.ON e.department_id = d.id— the match condition; unmatched rows getNULLfor the opposite side's columns.- In this clean dataset every employee has a department and every department
has staff, so no
NULLgaps appear — but the structure is what makes integrity checks possible.
Spotting orphans
Add WHERE d.id IS NULL OR e.id IS NULL to a FULL JOIN to list only the
rows that failed to match on one side — a classic data-quality report.
Variations
Find employees or departments with no counterpart:
Employees & Departments
Loading database engine...
Common Mistakes
- Forgetting
OUTER. In standard SQL the keyword isFULL OUTER JOIN; some engines acceptFULL JOINas shorthand (DuckDB does). - Assuming all rows match. A
FULL JOINdeliberately keeps unmatched rows, so expectNULLs and handle them withCOALESCEif you need a placeholder. - Using it when you only need one side. If you only care about keeping the
left table, a
LEFT JOINis simpler and clearer.