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;
> 1
> sqlite> select NULL = NULL;
> sqlite>

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 
a flag.

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

Reply via email to