Re: "IS NOT NULL" != "NOT NULL"

Vince Vielhaber <vev(at)michvhf(dot)com> writes:
> I had a hell of a time with that at first too. What you need to
> understand is that NULL isn't necessarily empty as you would expect.
> It's not the same as a null string - a null string actually has a
> real definition, a zero length string. I probably didn't help much.

Right. The common phrase "null string" doesn't help to reduce the
confusion any; perhaps "empty string" for zero-length string would
be a better phrase to use when you are working with SQL. NULL is
absolutely not the same as an empty string. NULL is outside the
domain of normal data for every datatype; it is better thought of
as the absence of a value than as any particular value.

I've been told that Oracle fails to distinguish empty strings from
NULL, which if true is a clear violation of the SQL specification.
If you're used to Oracle then that might help explain your confusion :-(

Another problem is that SQL's boolean operations act as though NULL
is the logical value UNKNOWN, rather than explicitly setting up a
boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
While the rules for propagation of NULL happen to be similar to the
results that logic dictates you get for UNKNOWN, this is still a kind
of type pun, and it doesn't help to reduce the confusion any.

regards, tom lane