Thanks, I just couldn't remember English for 'ternary'.
Remembering the and/or tables for the logic became trivial
when I replaced 'NULL' for 'UNKNOWN' but 'UNKNOWN' doesn't exist
as an SQL keyword. I suspect the founding fathers chose NULL over
words like UNKNOWN to try avoid philosophical debates since there are
two meanings for null values, one that the data exists but is unknown,
that the data simply doesn't exist, such as date of death for a live
But I think this doesn't really belong to a mysql forum but rather to a
general SQL discussion
so I'll try not to comment on that anymore.
Cc: jschwartz@stripped; mizioumt@stripped
Sent: Thu, 16 Nov 2006 2:55 PM
Subject: Re: finding NULL records
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
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
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
>> result values of TRUE or FALSE.
>> 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
>> 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
Beyond that, it often becomes implementation-dependant.
> The result of all but two expressions will be NULL (not 0 or 1) if
> side is NULL. The two exceptions are the operators IS NULL and IS NOT
> > SELECT NULL = NULL; /* result is NULL */
> SELECT NULL IS NULL; /* result is 1, or TRUE */
> SELECT NULL IS NOT NULL; /* result is 0, or FALSE */
-- Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Check Out the new free AIM(R) Mail -- 2 GB of storage and
industry-leading spam and email virus protection.