NULL represents a missing or unknown value. A normal equality JOIN does not match NULL to NULL because NULL represents an unknown value rather than a regular comparable value.
NULL values can cause expected relationships to be missing from JOIN results and can affect data analysis.
If an employee has a NULL DepartmentID, an INNER JOIN to Departments will not find a matching department.
SELECT
e.EmployeeName,
d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;Common in incomplete customer data, optional relationships, historical records, and data migration.
- NULL does not equal NULL.
- INNER JOIN excludes unmatched NULL relationships.
- LEFT JOIN can preserve rows containing NULL foreign keys.
- NULL handling should be considered during analysis.
- Use IS NULL when specifically checking for NULL.
Does NULL equal NULL in SQL?
No. NULL represents an unknown value, so NULL = NULL does not evaluate to true.
Which JOIN can preserve rows with NULL foreign keys?
A LEFT JOIN can preserve the row from the left table even when no matching row exists.