Empty result set when matching NULL?

Question

If I run a statement such as SELECT * FROM stuff WHERE something=NULL I will get an empty result set.

I know that the solution is to use something IS NULL. I also understand why: SQL interprets NULL as unknown, and it makes logical sense that if something is unknown, it can’t match something else.

What puzzles me is the meaning of the result set. I have always understood that you can’t match something=NULL, so why is is acceptable? It would suggest that under some circumstances, one might get a different result?

Alternatively, why does SQL not accept something=NULL as an idiom equivalent to IS NULL? It wouldn’t be the only idiom which doesn’t have a literal interpretation (such as count(*) vs SELECT *).

The question is: since something=NULL is not an error, does it mean something useful?

Answer 1

You can compare things to NULL.

something=NULL is not an error.

It is a Boolean expression that evaluates to unknown. SQL uses three valued logic. The possible results of a Boolean expression are true, false or unknown.

The WHERE clause only returns rows where the predicate evaluates to true.

Whilst comparing to a literal null with =null is rarely (if ever) useful it is certainly useful to be able to use an col = something predicate against a column which may contain nulls and the semantics of how = something operates against null are the same there.