Re: "= null" & "is null"

From: Norris <johnnie_at_cooper.com.hk>
Date: 29 Aug 1999 09:06:52 GMT
Message-ID: <7qat7c$rvt$1@adenine.netfront.net>

Maybe I need to change my question to the difference between "<> null" and "is not null".

Jim Ski <jimski_at_pacificnet.net> wrote:
> 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/

--
Have several nice days...

Opinions are mine and do not necessarily reflect those of the Corp.

http://www.ntfaq.com

http://www.jsiinc.com/reghack.htm

http://www.oconnell.net/Sybase_FAQ

http://www.cyberport.com/~tangent/programming/winsock/

===================================================================

Received on Sun Aug 29 1999 - 04:06:52 CDT