List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 1 2007 8:45am
Subject:Re: IS NULL Question
View as plain text  


> I'm having problems understanding NULL. I grasp what a NULL value is,

Actually, you don't :-)

NULL is not a value, it's the lack of value and a state.

A column can have 2 states: NULL or NOT NULL, which is part
of the reason why SQL allows for the IS NULL and IS NOT NULL
clause as opposed to checking for certain values with the equals
operator ( MyColumn = 'My 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.)

If the set is empty, the columns aren't NULL.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
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