List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:November 16 2006 7:55pm
Subject:Re: finding NULL records
View as plain text  
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

Thread
finding NULL recordsAlan Nilsson31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsAlan Nilsson31 Oct
    • Re: finding NULL recordsChris31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • RE: finding NULL recordsJerry Schwartz31 Oct
    • Re: finding NULL recordsMartijn Tonies31 Oct
      • Re: finding NULL recordsmizioumt15 Nov
        • RE: finding NULL recordsJerry Schwartz15 Nov
          • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsmizioumt17 Nov
          • Re: finding NULL recordsmizioumt17 Nov