- NULL is not the number zero.
- NULL is not the empty string () value.
It would clearly be incorrect to include a quantity of 0 for the plums record, because that would imply that we had no plums in inventory. On the contrary, we might have some plums, were just not sure.
Databases treat NULL values in a special way, depending upon the type of operation that it is used in. When a NULL value appears as an operand to an AND operation, the operations value is FALSE if the other operand is FALSE (there is no way the expression could be TRUE with one FALSE operand). On the other hand, the result is NULL (unknown) if the other operand is either TRUE or NULL (because we cant tell what the result would be.)
The OR operand treats NULL values in a similar fashion. If the other operand is TRUE, the result of the OR is TRUE (because the real value of the NULL operand doesnt matter.) On the other hand, if the other operand is either FALSE or NULL, the result of the OR operation is NULL.
There are two special operands used to test for the presence of the NULL value. ISNULL returns TRUE only when the supplied operand has a NULL value. Conversely, ISNOTNULL returns TRUE when the supplied operand does not have a NULL value. These are quite important functions. Avoid one of the most common database mistakes: testing an operand for a NULL value by comparing it to the empty string or zero is not correct!
Thats NULL in a nutshell! Take a few moments to review these concepts and get them straight in your head. Youll be glad you did down the road!
Fruit Stand Inventory