Re: "= null" & "is null"

In order to understand the difference between the two representations, you have to know what NULL is. NULL means UNKNOWN. It does NOT mean a string of zero length or a numeric value of zero. An example of usage might be a field call FaxNumber. If the database record were to indicate that no FAX machine exists for the record entity, a zero length string would be an appropriate value. If you didn't know whether or not the entity had a FAX machine a NULL in the FaxNumber field would be appropriate. Because of the lack of knowledge the NULL portrays, the use of NULL uses different rules of operation than standard data types like numbers and strings. For example NULL + anything not NULL is always NULL. In English, if I add an unknown amount to the number 2 my total is still unknown. Similarly, if a function argument is NULL, my result is always NULL. For example function(NULL) is NULL. Most importantly, in answer to your question, I can not test equality against a NULL. The expression (NULL = NULL) always evaluates to NULL and never to TRUE or FALSE. The designers of SQL decided it was important to be able to detect an unknown value in a table field and so they came up with "IS NULL" as a way to test equality. It would be used as follows:

    SELECT * FROM TABLE1
    WHERE FaxNumber IS NULL;

The designers of SQL also knew that it was important to be able to assign a NULL value to a field and so they used the equal sign (=) as an assignment operator (not as an equality symbol). It would be used as follows:

    UPDATE TABLE1
    SET FaxNumber = NULL
    WHERE ID = 1234; The only place where you will see "= NULL" is as an assignment in an update statement.

Note that the differentiation between equality and assignment is prevalent in other languages.

Language Equality Operator Assignment Operator

C                        ==                              =
Pascal                =                                :=
SQL                   IS NULL                      = NULL

js

Norris wrote in message <7q3936$37f$2_at_imsp009a.netvigator.com>...
>What is the difference between = null and is null?
>
>--
>--
>http://www.washington.edu/pine/faq/
Received on Sat Aug 28 1999 - 23:06:11 CDT