Re: NULLS and User Input WAS Re: multimaster

Richard Huxton wrote:
PFC wrote:
>>> NULL usually means "unknown" or "not applicable"

Andrew Sullivan wrote:
>> Aaaargh!  No, it doesn't.  It means NULL.  Nothing else.
>> If it meant unknown or not applicable or anything else, then
>>     SELECT * FROM nulltbl a, othernulltbl b
>>         WHERE a.nullcol = b.nullcol
>>
>> would return rows where a.nullcol contained NULL and b.nullcol
>> contained NULL.  But it doesn't, because !(NULL = NULL).

(a == b) <=> ( (a -> b) AND (b -> a))

| a  |  b  | a->b | b->a | a==b |
|----|-----|------|------|------|
| F  |  F  |  T   |  T   |  T   |
| F  |  T  |  T   |  F   |  F   |
| F  |  U  |  U   |  U   |  U   |
| T  |  F  |  F   |  T   |  F   |
| T  |  T  |  T   |  T   |  T   |
| T  |  U  |  U   |  T   |  U   |
| U  |  F  |  U   |  U   |  U   |
| U  |  T  |  T   |  U   |  U   |
| U  |  U  |  U   |  U   |  U   |

Ergo, (UNKNOWN = UNKNOWN) is UNKNOWN.  Similarly for (UNKNOWN != UNKNOWN).

Where NULL differs is that (NULL = NULL) is FALSE, and (NULL != NULL) is FALSE.

The similarity is that with NULL, SQL is not exactly saying (NULL = NULL) is
FALSE so much as that it's not TRUE.

NULL follows Zen-valued logic, not 3-valued, and that seems somehow
appropriate to me.

--
Lew