i think NULL means the value has not yet been set, thus although it is

not equal to 2 in one sense

it also has no value at all so it is also

not not equal to 2 as well (eg NULL isn't zero, you aren't comparing like with like)

... heck, this sounds like a stupid answer, but i know what i mean.

I like the way MySQL clarifies it ...
[dev.mysql.com...]

ok, so I still don't get it, but I can try to accept it. I ask myself is NULL == '2'? No is isn't. The documentation says this:

>>In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

There must be situations beyond my simple experience where the above actually does something usefull, I just can't think of anything.

So should all!= comparissons look like this:

SELECT * FROM myTable WHERE color!= '2' OR color IS NULL

That exactly how you should do it if a NULL value is supposed to match your "is not equal to" comparison.

Better yet, consider not allowing NULL.

>>Better yet, consider not allowing NULL.

i'd agree with DrDoc on this,

i (nearly) always set the default value of fields to be 0 or '' partly to avoid the NULL problem

I think that not allowing NULLs and/or defaulting a column to zero or '' is not really the best approach.

As explained in the SQL docs, NULL clearly has a different meaning to 0 and the empty string.
Referring to the original query, I guess that null values imply that the color is "unknown" - in this case, using the value "0" may not be appropriate, as this could refer to a color value in itself, maybe...

I personally prefer to keep the unique meaning conferred by NULL values and deal with the slight inconvenience in queries as required.

Think of it this way: if the color is NULL, there ISN'T a color.

Now, if there isn't a color, how can you compare it to anything?

How do you compare something that doesn't exist? Is the non-existent house across the street blue or purple? Or not blue or not purple?

Doesn't compute...

>>NULL clearly has a different meaning to 0 and the empty string.

yes FalseDawn i agree with you, however in many cases, i think that NULL is mistakenly used to mean nothing/zero (rather than not allocated) ... just as it has been used by the op in this case ...

thus if you are inexperienced, setting the value to 0 or '' can save confusion and unexpected results when querying the db, better still of course is to not allow thew field to be NULLable unless you really know what NULL means and is used for.

The best way to think of NULL is "unknown".

If you have a column for price with NULL in it, it does not mean the item is free - it means that the price has not been determined yet. Or if you have NULL for faxnumber, it doesn't mean the person doesn't have a fax - it means you don't know what their faxnumber is.

"SELECT * FROM myTable WHERE color!= '2'" does not return rows where color is NULL, because it possibly could be '2'. If your intention is to return NULL values, then your code should read:

"SELECT * FROM myTable WHERE color!= '2' OR color IS NULL"

The rules to remember for NULL's are:

1. NULL's fail all tests. The only way to get a NULL value from a WHERE clause is to say IS NULL (or IS NOT NULL for the reverse).

2. NULL's propagate. In any calculation, once a NULL is encountered the answer is always NULL. If you are adding three columns - SALARY, BONUS and COMMISSION - and one of the columns has NULL for a value (say BONUS), the result is NULL (unknown). The logic here is that since we don't know what the BONUS amount is, we can't know the total amount of SALARY+BONUS+COMMISSION.

Thanks syber, this actually DOES make sense. I appreciate the explanation.