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?
Best Answer
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
orunknown
.The
WHERE
clause only returns rows where the predicate evaluates totrue
.Whilst comparing to a literal
null
with=null
is rarely (if ever) useful it is certainly useful to be able to use ancol = something
predicate against a column which may containnulls
and the semantics of how= something
operates againstnull
are the same there.