Forum Moderators: open
That's about the dumbest thing I've ever heard of. What's up?
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.
>>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
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
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.
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...
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.
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.
