>>> SELECT * FROM tests WHERE test_id IS NULL
>> Yes, that works, but I was also trying SELECT * instead of just the
>> 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.
>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.
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,
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.
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!