Re: why the need for is null?

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> If you want it to match perhaps you should forget NULL and use '' (zero
> length string) instead.

Yes. The SQL semantics essentially define NULL as meaning "unknown",
which does not mean "empty" or "not applicable" or anything like that
--- it means "I am not sure what this field should contain". The
spec's semantics work properly under that interpretation. For other
interpretations they will confuse and distress you.

It's better to choose a specific non-null value to represent "empty",
if you want the semantics that "empty" is equal to "empty".

BTW, the actual spec text that mandates this is SQL99 Part 2 section
8.2 <comparison predicate>, general rule 1:

1) Let XV and YV be two values represented by <value expression>s X
and Y, respectively. The result of:

X <comp op> Y

is determined as follows:

Case:

a) If either XV or YV is the null value, then

X <comp op> Y

is unknown.

b) Otherwise, [ etc etc ]

It may be illuminating that the boolean value "unknown" is the same as
(or at least the standard does not distinguish it from) boolean NULL.
Cf. section 4.6:

The data type boolean comprises the distinct truth values true and
false. Unless prohibited by a NOT NULL constraint, the boolean
data type also supports the unknown truth value as the null value.
This specification does not make a distinction between the null
value of the boolean data type and the unknown truth value that is
the result of an SQL <predicate>, <search condition>, or <boolean
value expression>; they may be used interchangeably to mean exactly
the same thing.

regards, tom lane