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