One of the fun facts about SQL Server and the relation model is the

whole concept of three valued logic. Now I’m not breaking any new

ground here I am just touching on something that trips people up when

they expect a result and don’t get it due to the magic of NULL’s. To be

honest, I’m no exception to falling into the unknown from time to time.

Codd laid out 12 fundamental rules of what a relational database system should conform to if it is to be considered truly relational.

Rule 3: Systematic treatment of null values:

The

DBMS must allow each field to remain null (or empty). Specifically, it

must support a representation of "missing information and inapplicable

information" that is systematic,

distinct from all regular values (for example, "distinct from zero or

any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

This rule, above all others has probably caused me the most heartburn over the years.

I’ve

spent more time that I like to admit reading papers and books from

pioneers of the relational model, E.F. Codd, C.J. Date. One of the

great debates that carried on until the passing of Codd was over NULL

and three valued logic in general. To give you an idea of how big and

problematic NULL’s are, It’s been put forth that maybe we need to get

rid of them all together or move deeper into the rabbit hole and make

it 4 valued logic breaking NULL into different kinds of place holders

for unknown.

Understand that every piece of data falls into a

domain of some sort. 0 is a integer type. ‘’ is a empty string. NULL

isn’t in any domain, or value type. It doesn’t represent something at

all it is a place older period.

I’ve heard 3 valued logic described as yes/no/maybe but that isn’t accurate, it is true/false/UNKNOWN.

So

the only logical thing that can happen to UNKNOWN is unknown. What’s

even worse is UNKNOWN technically can’t be equal to UNKNOWN or NULL =

NULL.

How do you know they are equal if they are both unknown?

For example:

select 1 where NULL = NULL

returns nothing since NULL can’t be equal to anything including NULL we don’t get a NULL back or the 1 back we tried to select.

select 6227 * 453 / 238 + NULL 

returns NULL

which makes since on the surface to almost everyone I work with.

select NULL / 0

returns NULL

To some folks this is confusing in a traditional programming since

anything divided by zero gives us an error of cannot divide by zero.

Since NULL is the place holder for UNKNOWN there is no way to evaluate the statement other than UNKNOWN or NULL!

This must also carry through for string manipulation as well.

For example:

select 'here' + 'is ' + NULL 

returns NULL.

Again it is the old how can you concatenate something to the unknown problem.

Now with all this in our little busy heads we finally think we

understand the problem in it’s fullness, but we don’t (or I always

don’t).

Where things can get really sticky is in any kind of aggregate

situation SUM(), AVG(). Generally, all aggregations have a NULL

elimination step built into them.

So lets say we have a table that looks like this:

(this isn’t an article on table design so don’t sweat the lack of a key or the duplicate data in both columns)

create table myNumbers
(
    Col001 int,
    Col002 int
)
go
insert into myNumbers (Col001,Col002) VALUES (100,100)
insert into myNumbers (Col001,Col002) VALUES (200,200)
insert into myNumbers (Col001,Col002) VALUES (300,300)
insert into myNumbers (Col001,Col002) VALUES (NULL,300)

select avg(Col001) from myNumbers

select avg(Col002) from myNumbers

We get:

-----------

200

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

-----------

225

(1 row(s) affected)

What happens when the evaluation of the aggregation occurs there is

no way to evaluate the NULL so that row is dropped and all the sudden

you numbers look horribly wrong.

If I hadn’t put this to output to text we may have merrily trucked

along and accepted that 200 was what we were expecting and not the 255

we really wanted due to the treatment of the NULL.

Since it is a warning and not an error our program won’t complain

ether it will be more than happy to accept the result since it is valid

one.

The only exception to this in general is using COUNT() since you can

count a place holder just like you would a value type it isn’t

evaluating the data held just that a row exists whether we know what is

is in it or not.

I’ve only just scratched the surface of the unknown with this little

post. I haven’t covered grouping or JOIN’s and I may in a later post.

My goal is simply to remind myself that dealing with NULL is never a trivial matter.

-Wes