List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 31 2006 8:08am
Subject:Re: finding NULL records
View as plain text  
>>> SELECT * FROM tests WHERE test_id IS NULL
>> 
>> Yes, that works, but I was also trying SELECT * instead of just the
>key 
>> field (just a typo in the example).  The problem was in the equal sign
>
>> versus the 'IS' operator.  Any reason why MySQL does not honor 
>> <field>=NULL?  Seems kind of odd.
>
>Sql standard says you use IS NULL.
>[/snip]
>
>And the reason for that is because nothing is actually equal to NULL.
>For a field to be equal to NULL it would actually have to contain NULL,
>in which case it would not be actually NULL.

What?

NULL doesn't equal NULL because NULL means "unknown".

A column can have 2 states: known or unknown (NULL). If the
case of a "known" state, the column has a value, in cause of unknown,
it's NULL.

You cannot compare to "unknown". So this is why the SQL standard
says: you either ask for a specific value (eg: myid = 1002) or you ask
for a state: "myid IS NULL" or "myid IS NOT NULL" (= filled).

It has nothing to do with "contain NULL" or whatever.





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
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