List:General Discussion« Previous MessageNext Message »
From:Gerald L. Clark Date:May 1 2007 4:03pm
Subject:Re: IS NULL Question
View as plain text  
Kevin Hunter wrote:
> At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote:
> 
>> I'm having problems understanding NULL. I grasp what a NULL value is,
> 
> 
> A NULL value is rather an oxymoron.  It'd be more accurate to say  that 
> NULL means "absence of a value".
> 
>> but I can't get NULL to perform correctly. For instance, if I do a
>> Select statement, I see that I have columns with a NULL value.
>>
>> select first, last, suffix  from persons LIMIT 5;
>> +-----------+----------+--------+
>> | first     | last     | suffix |
>> +-----------+----------+--------+
>> | Benjamin  | Page     | NULL   |
>> | Jonathan  | Watson   | NULL   |
>> | Jose      | Thorson  | NULL   |
>> | Alejandro | Nickels  | NULL   |
>> | Griselda  | Richards | NULL   |
>> +-----------+----------+--------+
>> 5 rows in set (0.01 sec)
>>
>> Logically, it seems that a Select statement should find these five  plus
>> any other NULL values in the suffix column. However, such a select
>> statment returns an empty set.
>>
>> mysql> select first, last, suffix  from persons where suffix IS NULL;
>> Empty set (0.00 sec)
>>
>> Does anyone see what I'm doing wrong? (Thanks in advance for any  help.)
> 
> 
> Caveat emptor: I haven't tested this in about a year.
> 
> Are you perchance using a table type of MyISAM?  I seem to recall  that 
> MyISAM has a hard time actually representing NULL internally.   [ ... 
> Thinks for a minute ... ]  I remember something about spaces,  like, I 
> think I did
> 
> INSERT ( val1, val2, NULL ) INTO myTable;
> 
> and was only able to get the tuples back when I did
> 
> SELECT * FROM myTable WHERE col3 = ' ';  -- a space character
> 
> If this is the case, you might consider using a different table type,  
> such as InnoDB.
> 
> HTH,
> 
> Kevin
> 

MyISAM has no problem representing NULL.

-- 
Gerald L. Clark
Supplier Systems Corporation
Thread
IS NULL QuestionJohn Kebbel1 May
  • Re: IS NULL QuestionJeremy Cole1 May
    • Re: IS NULL QuestionJohn Kebbel1 May
  • Re: IS NULL QuestionMartijn Tonies1 May
    • Re: IS NULL QuestionLucas.CTR.Heuman1 May
  • Re: IS NULL QuestionKevin Hunter1 May
    • Re: IS NULL QuestionGerald L. Clark1 May
      • Re: IS NULL QuestionKevin Hunter1 May
    • RE: IS NULL QuestionJerry Schwartz1 May
      • Re: IS NULL QuestionBaron Schwartz1 May
    • batch modeCharles Brown2 May
      • Re: batch modeMogens Melander2 May
        • Re: batch modeMogens Melander2 May