On 5 Jan 2018, at 9:03pm, Doug Currie <doug.cur...@gmail.com> wrote:
> NULL is not equal to NULL, though NULL is NULL.
> sqlite> select NULL IS NULL;
> sqlite> select NULL = NULL;
To expand on this, in SQL NULL has a special meaning. It means "value unknown
or missing". It turns up naturally in some places, for example, in JOINs where
the joined row doesn’t exist. Don’t think of NULL as a value. Think of it as
Because of this, testing for NULL is unexpectedly complicated, as shown by the
above. Two values might both be unknown, but this doesn’t automatically mean
that they have the same value. Yet an unknown value is indeed unknown, even if
we don’t know what it is. That’s why you get the results Doug posted above.
Many examples I see which store NULL as a value in the database don’t really
mean NULL, they mean 0 or the empty string, or an empty list, and changing the
database to store a more appropriate value lets the programmer strip many lines
of complicated logic out of their software.
sqlite-users mailing list