Re: String Comparison and NULL

Andreas Kretschmer <akretschmer(at)spamfence(dot)net> writes:
>>> ... and I do something like "select id where animal <> 'Cat';" then
>>> shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5.
>>> NULL is not 'Cat'. I realize that if I were testing for NULL itself I

> NULL is nothing, you can't compare something with nothing.

A better way to think about it is that NULL means UNKNOWN. Thus
the result of NULL <> 'Cat' is not FALSE but UNKNOWN (ie NULL)
--- if you don't know what the value is, you don't know whether or not
it's equal to any specific other value.

The other mistake novices typically make is to expect that
NULL = NULL will yield TRUE. It doesn't, it yields NULL,
because again you can't say whether two unknown quantities
are equal.

You can hack around this behavior to some extent with
IS DISTINCT FROM, but generally the right thing is to redesign
your data representation. Trying to make NULL act like a normal
data value is almost always going to lead to tears in the long run.

regards, tom lane