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
