NULL Semantics
Most comparisons to null
return null
. Exceptions are generally in functions or comparisons specifically to handle null
, such as IS NULL
.
When the outcome of a comparison is null
, this will be coerced to false
when used in a filter (WHERE
or HAVING
) but return as null
in a SELECT
statement.
To demonstrate, first a null
comparison in a SELECT
statement:
This returns null
for all values.
Using a null
comparison in a WHERE
statement will always result in false
.
Returns an empty set, this is true even for inequality tests:
Also, returns an empty set.
Note
null
comparison returning null
holds true even for null = null
. Do not test for null using an equals condition, use IS NULL
.