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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
