Hi all,
may I propose some slight corrections in wording ?
Inserted below at the appropriate places:
Jerry Schwartz wrote (re-ordered):
>
>> -----Original Message-----
>> From: mizioumt@stripped [mailto:mizioumt@stripped]
>> Sent: Wednesday, November 15, 2006 2:22 PM
>> To: m.tonies@stripped; mysql@stripped
>> Subject: Re: finding NULL records
>>
>> [[...]]
>> This is what happens when one of the most innovative concepts of SQL
>> gets overlooked
>> or understressed which it seems to be in most modern SQL books.
>> The thing is that SQL's logic isn't binary. Rather it's built over
>> three values: TRUE, FALSE, NULL.
Nearly: The three values are typically called TRUE, FALSE, and UNKNOWN.
A logic based on three values is then called "ternary", AFAIR.
>> You can indeed compare anything to NULL:
You sure can, like you can compare apples to oranges - just the result
is not meaningful.
In SQL, NULL is no value, it rather describes the absence of any value.
What often gets overlooked: "absence of value" is different from
"empty", so an empty string is a non-NULL value !
>>
>> x = NULL
>>
>> in most systems is a valid syntax. The result is NULL if x is
UNKNOWN
>> [[...]]
Even if it is valid syntax, you cannot really use it:
Comparing something known (some value) to something unknown (no value
present) cannot give you any definite result,
so in SQL for *any* comparison operator "op" ( = < > <> ... LIKE ... )
and *any* known (= non-NULL) value x (column, literal, expression, ...)
the result of
x op NULL
is UNKNOWN.
As a rough analogy, consider comparing some visible object to one which
is hidden behind a curtain:
You cannot tell whether they are identical or not, which one is larger,
... , so the result is UNKNOWN. Especially, the result is *not* FALSE !
If such a predicate is used in a WHERE condition, like in
SELECT x, y, z FROM tab WHERE x op NULL
then "x op NULL" evaluates to UNKNOWN, this is different from TRUE, and
so the WHERE condition is not satisfied.
>>
>> x IS NULL
>>
>> on the other hand isn't a binary operator, it's an UNARY one with the
>> result values of TRUE or FALSE.
Correct.
>>
>> The whole join theory obviously revolves around NULL treatment but
>> otherwise the NULL algebra becomes
>> fuzzy. I'm not sure what the standards say to the question of sorting
>> NULLs, or whether each null constitutes
>> a separate group as in GROUP BY, or all can be grouped together, but
>> the actual implementations
>> often make the answers configurable.
I don't have the standard at hand either, the only thing I remember is
that it requires NULL to sort either less than or greater than all
non-NULL values.
Beyond that, it often becomes implementation-dependant.
>>
>> [[...]]
>>
>
> The result of all but two expressions will be NULL (not 0 or 1) if either
UNKNOWN
> side is NULL. The two exceptions are the operators IS NULL and IS NOT NULL.
>
> SELECT NULL = NULL; /* result is NULL */
UNKNOWN
> SELECT NULL IS NULL; /* result is 1, or TRUE */
> SELECT NULL IS NOT NULL; /* result is 0, or FALSE */
Regards,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
