Full Outer Join in SQL: A Comprehensive Guide

Estimated study time: 14 minutes. Every row from both tables, matched or not.

A FULL OUTER JOIN returns every row from both tables. Where a row on either side has no match, the columns from the other table come back as NULL. It's effectively a LEFT JOIN and a RIGHT JOIN combined.

Basic Syntax

SELECT columns
FROM TableA
FULL OUTER JOIN TableB ON TableA.key = TableB.key;

A Practical Example

SELECT Employees.EmpName, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;

This returns every employee — including ones with no department — and every department — including ones with no employees — filling in NULL wherever a match is missing.

Finding Unmatched Rows on Either Side

SELECT Employees.EmpName, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID
WHERE Employees.DeptID IS NULL OR Departments.DeptID IS NULL;

This isolates exactly the rows that don't have a counterpart on the other side — handy for finding orphaned records in either direction.

When to Use It

Reach for a FULL OUTER JOIN when you need a complete picture from both tables — for example, reconciling two lists to see what's missing from each.