timothy.bates (7/23/2010)
We have all tried to explain that NULL does not equal 0 or NULL to an end user and often the response I've gotten is "That's STUPID!" This raises the question, What does null really mean? It is more involved than Schrödinger's cat delima. Null brings into question is there a cat or radioactive isotope in the box. My spin is that NULL means "I don't know", or that it has not intentionally been assigned a value by the system or a user. A value of 0 or empty string can be inferred as an intentional assignment. Often we may want to establish a starting point (e.g. shopping cart total). Here a default should be used and not allow nulls. Other times you may have a Yes/No question. Do you use a default? By using a default you have forced an answer to all questions. In fact the answer to the question may be "I don't know" or "I abstain".In my opinion NULLs serve a purpose. They are the love child of two people arguing semantics.
Null in SQL means only one thing: the absence of a value. In an execution context, local variables can fail to have a value (eg when being assigned in a select statement, or when they have been declared but no value has ever been assigned) - if they do, they are null. NULL occurs in a table when there is no value in the database for this column and row. It doesn't of course indicate why no value is present - just that none is.
In particular it doesn't mean that the value is unknown - it may be known but bad code failed to enter it when it should, it may be known but there hasn't been time to enter it yet, it may be known but secret and withheld from the database, it may be be that the attribute is not present because for this row it doesn't exist (eg wife's maiden name for a bachelor, that is inapplicable as opposed to unknown - although in SQL, which doesn't have a separate NULL to cater for this case, it is usually bad practise to have a schema with columns that are sometimes inapplicable), and so on.
It is unfortunate that the ANSI and ISO people persist in using a definition of NULL that claims that it is a value, when it is not a value but rather an indicator that no value is present. I think that that definition has caused some of the confusion that surrounds NULL. It's even more of a pity that some DBMS suppliers (including Microsoft) continue to treat NULLs in expressions by default as values equivalent, depending on context, to a 0 integer or zero length string or whatever seems to fit in the value domain; this has caused most of the confusion over NULL; the one cheering thing on that front is that soon the present default treatment of NULL in expressions in SQL Server will cease to be the default and won't even be able to be selected, only the proper treatment of NULL will be possible.
Reasonable arguments can be made either way for how REPLACE ought to operate when there second parameter matches nothing in the first and the third parameter is NULL. "NULL in means NULL out" (as seen in several earlier comments) isn't one of those arguments - operators like COALESCE and ISNULL are obvious counterexamples to that argument. Personally I like the simplicity of the current behaviour but dislike the hoops I sometimes have to jump through to programme around it - but despite those hoops (and some severe doubts about the complications the current behavious causes to any functional semantics for T-SQL) I think the current behaviour is probably best - KISS usually wins.
Tom