Nulls and Comparisons
Because a null is not a value, it cannot be compared to any other value (including another null value). For example, the following WHERE clause evaluates to false if one or both of the columns is null:
WHERE columna = columnb
Similarly, the WHERE clause:
WHERE columna < 10 OR columna >= 10
is true for all numeric values of columna, but false if columna is null.
Although generally null values are ignored in comparisons, there are comparisons that can be used that do allow null values to be matched against. It is possible to test for the presence of a null value using IS NULL so a WHERE clause could be constructed that allowed null values to match such as:
WHERE columna=columnb OR (columna IS NULL AND columnb IS NULL)
An operator that embodies this behavior is IS NOT DISTINCT FROM. It behaves like = (equals) and will compare null values as will its <> (not equal) equivalent IS DISTINCT FROM. The following WHERE clause evaluates to false unless both columna and columnb are equal or both columna and columnb are NULL:
WHERE columna IS NOT DISTINCT FROM columnb
Last modified date: 08/29/2024