Null Follow up

sene bauman said:

Thanks Wes, you made me smile:) Its always nice to know you are among friends who share your pain.  I can't count the number of times I have fallen into the NULL trap myself. But I am getting better, at least with aggregates.

I would however take exception to a minor point. NULL is not "unknown". In fact, it is precisely known. It simply means that the container contains no value, regardless of the data type.  If it were unknown then the IsNull() function would never work. :)

But the debate on the issue of whether 3 state logic true/false/empty is useful will continue.  Of course, regardless of its purpose, the pain associated with NULL can be relieved if default conditions are set on the server.  For example, SET ANSI_NULLS is OFF/ON.  For aggregates, I also always use sum(isNull(blah,0)).

Hey Sene,

Glad you enjoyed it! I used UNKNOWN because that is exactly the definition Codd gives NULL.

He stated it is unknown due to the fact it can represent any value including no value and when evaluating NULL that must be taken into consideration, according to ternary logic.

What it boils down to is anything evaluated = or <> to NULL, including NULL is NULL. This is clarified in the ANSI 92 standard and implemented in SQL Server in the 7.0 time frame if I’m not mistaken.

Before that NULL could be evaluated and return a result set which is a violation of the ternary logic model.

That is why we have the SET ANSI_NULLS option in SQL Server to begin with, so if you were upgrading from say 6.5 or had a ton of older code in place that expected NULL to behave the old way you didn’t have to re-write everything just to upgrade to the latest and greatest version of SQL Server.

To correctly handle NULL evaluation the key reserved phrases are IS NULL and IS NOT NULL this allows you to evaluate NULL to NULL and duplicate = or <> operations.

I almost covered the IsNull() function but kind of ran out of steam.

IsNull() is a T-SQL specific function and simply says if this field contains the NULL placeholder then give it a specific value, it doesn't assume empty. I would point you to COALESCE() which is the ANSI standard function for handling NULL's.

Don’t work around the problem of NULL work with it to yield it’s full potential!

I feel like I'm channeling Joe Celko....