FULL OUTER JOIN returns all matching rows plus unmatched rows from both tables. Missing values on either side are represented by NULL.
FULL OUTER JOIN is useful when you need a complete comparison between two datasets, including records that exist on only one side.
Compare employees and payroll records to identify matches and missing records.
SELECT
e.EmployeeID,
e.EmployeeName,
p.EmployeeID AS PayrollEmployeeID
FROM Employees e
FULL OUTER JOIN Payroll p
ON e.EmployeeID = p.EmployeeID;It is useful for reconciliation, data migration checks, system comparisons, and data-quality analysis.
- FULL OUTER JOIN keeps unmatched rows from both tables.
- Matching rows are combined.
- Missing values appear as NULL.
- It is useful for reconciliation.
- Not every database system supports FULL OUTER JOIN.
What does FULL OUTER JOIN return?
It returns matching rows and unmatched rows from both tables.
When is FULL OUTER JOIN useful?
It is useful when comparing two datasets and identifying records missing from either side.