Unmatched records are rows from one table that do not have a corresponding row in another table based on the JOIN condition.
Identifying unmatched records helps detect missing relationships, incomplete data, and differences between systems.
Find employees whose DepartmentID does not have a matching department record.
SELECT
e.EmployeeID,
e.EmployeeName,
e.DepartmentID
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;Useful for master-data validation, reconciliation, migration testing, compliance checks, and database cleanup.
- Unmatched rows have no corresponding record.
- LEFT JOIN can identify missing right-side matches.
- IS NULL is commonly used for detection.
- Incorrect keys can create false unmatched records.
- This is a common data-quality pattern.
How can you identify unmatched records?
Use an OUTER JOIN and check for NULL values on the side where the match is missing.
Why might an expected record appear unmatched?
Possible causes include missing data, incorrect join keys, formatting differences, or inconsistent values.