In SQL, how do you compare two nullable columns so that two NULLs count as equal?

## The problem A change-detection query asks "did this column's value change?" over a nullable column: ```sql SELECT s.id FROM staging s JOIN target t ON t.id = s.id WHERE s.email <> t.email; -- misses rows where either email is NULL ``` If one side is NULL the comparison is UNKNOWN and `WHERE` discards the row, so a value that changed from NULL to `'[email protected]'` — a genuine change — is reported as unchanged. Worse, the reverse case is also invisible: a value that changed from `'[email protected]'` to NULL does not show up either. Any code that diffs, deduplicates, or matches on nullable columns runs into this. ## The standard operator SQL defines a NULL-safe comparison built on the notion of two values being *distinct*: ```sql a IS NOT DISTINCT FROM b -- TRUE when equal, or when both are NULL a IS DISTINCT FROM b -- TRUE when they differ, or when exactly one is NULL ``` The defining property is that these predicates are **two-valued**: they always return TRUE or FALSE and never UNKNOWN. Full truth behaviour: | a | b | a = b | a IS NOT DISTINCT FROM b | |---|---|---|---| | 1 | 1 | TRUE | TRUE | | 1 | 2 | FALSE | FALSE | | 1 | NULL | UNKNOWN | FALSE | | NULL | NULL | UNKNOWN | TRUE | So the change-detection query becomes correct simply by swapping the operator: ```sql WHERE s.email IS DISTINCT FROM t.email ``` and now reports exactly the rows whose value differs, including transitions into and out of NULL. ## Portability This is one of the places where engines genuinely diverge, so state your assumption. PostgreSQL implements the standard `IS [NOT] DISTINCT FROM` spelling. MySQL provides the NULL-safe equality operator `<=>`, which is equivalent to `IS NOT DISTINCT FROM`. SQLite's `IS` and `IS NOT` operators compare like `=` but treat two NULLs as equal. Other engines may or may not have the standard form, so check the documentation for the one you target. The rewrite that runs everywhere is a plain disjunction: ```sql WHERE (a = b) OR (a IS NULL AND b IS NULL) -- NULL-safe equality WHERE (a <> b) OR (a IS NULL) <> (b IS NULL) -- avoid this shape; see below ``` The inequality direction is easier to write as the explicit three-branch form: ```sql WHERE (a <> b) OR (a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL) ``` Verbose, but unambiguous and portable. Each branch supplies a decisive TRUE or FALSE, so no UNKNOWN escapes to the top of the condition. ## The sentinel shortcut and its trap A popular shortcut replaces the NULLs with a value that "can never occur": ```sql WHERE COALESCE(a, '~none~') <> COALESCE(b, '~none~') ``` This works only under an assumption you must be able to defend: that the sentinel truly cannot appear in the data. The day a row legitimately contains `'~none~'`, the comparison quietly treats it as equal to a NULL and the bug is very hard to see. For numeric columns the usual `-1` sentinel is even riskier. It also has a secondary cost — wrapping a column in a function makes the predicate non-sargable, so an index on that column can no longer be used for a direct lookup. When you do need a sentinel (for example on an engine without the standard operator, inside a grouping key), pick a value that is structurally impossible for the domain and write a comment saying why. ## Related shapes - **Negation.** `NOT (a IS DISTINCT FROM b)` is exactly `a IS NOT DISTINCT FROM b`. Because these predicates are two-valued, `NOT` behaves normally around them — one of the few places NULLable columns keep boolean intuition. - **Row-wise comparison.** Some engines allow the operator on row values, comparing several columns at once. Support is uneven; the portable fallback is one NULL-safe comparison per column, ANDed together. - **Grouping versus comparing.** SQL is deliberately inconsistent here: `=` says two NULLs are not known to be equal, but `GROUP BY` and `DISTINCT` treat them as alike and collapse them. `IS NOT DISTINCT FROM` is the comparison operator that matches the grouping behaviour, which is a neat way to describe it in an interview. ## How to answer Name the operator, state the property that makes it work (two-valued, never UNKNOWN), give the portable `OR (a IS NULL AND b IS NULL)` rewrite, and flag the sentinel trick as conditional on an assumption about the data.