List:General Discussion« Previous MessageNext Message »
From:mizioumt Date:November 17 2006 6:42pm
Subject:Re: finding NULL records
View as plain text  
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: joerg@stripped
To: mysql@stripped
Cc: jschwartz@stripped; mizioumt@stripped
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: 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

________________________________________________________________________
Check Out the new free AIM(R) Mail -- 2 GB of storage and 
industry-leading spam and email virus protection.

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