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:
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.
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
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.
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 =
How do you know they are equal if they are both unknown?
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
which makes since on the surface to almost everyone I work with.
select NULL / 0
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.
select 'here' + 'is ' + 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
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
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
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
(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
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.