List:General Discussion« Previous MessageNext Message »
From:Kevin Hunter Date:May 1 2007 3:37pm
Subject:Re: IS NULL Question
View as plain text  
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
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