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
at least
two meanings for null values, one that the data exists but is unknown,
and another
that the data simply doesn't exist, such as date of death for a live
person.
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.
Thanks,
Michael
-----Original Message-----
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thu, 16 Nov 2006 2:55 PM
Subject: Re: finding NULL records
Hi all,
may I propose some slight corrections in wording ?
Inserted below at the appropriate places:
Jerry Schwartz wrote (re-ordered):
>> -----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 15, 2006 2:22 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
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
________________________________________________________________________
Check Out the new free AIM(R) Mail -- 2 GB of storage and
industry-leading spam and email virus protection.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
