SQL, data modeling, data quality, EDA, visualization · insight-1043
Use IS NOT DISTINCT FROM when NULL keys should match in a join or a comparison
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
You reconcile two extracts by joining on route and fare_class, and rows where fare_class is NULL in both tables vanish from the matched set.
Action
Join with a NULL-safe comparison (IS NOT DISTINCT FROM, or the dialect's equivalent such as the <=> operator), or replace NULL with an explicit sentinel only in a view built for the comparison. Count rows with NULL in each key column before joining.
Reason
IS NOT DISTINCT FROM treats two NULLs as equal; ordinary equality returns NULL for them.
Boundary
Reconciliations, change detection between snapshots, merges and deduplication on nullable keys.
Symptom or risk
An equality with NULL is neither true nor false, so those rows fall out of inner joins and appear as unmatched in both directions, which inflates the mismatch report.
Exceptions
Keys where NULL means "unknown", since two unknowns need not be the same value.
Trade-offs
NULL-safe comparisons can prevent index use in some engines.
Pitfalls
Coalescing to an empty string that collides with a real empty value.
Lessons and concepts
Claims and their sources
In PostgreSQL ordinary comparison returns NULL when either input is NULL, while IS NOT DISTINCT FROM treats NULL as an ordinary value so NULL IS NOT DISTINCT FROM NULL is true.