One of the best ways to test the knowledge of a database programmer is to ask very simple questions. I was reminded of this the other day whilst trying to put an explanation of a Relational Database into language which the proverbial Man in the Pub could understand. There is always a temptation to scurry for the cover of a thicket of long words to cover up ones wooly thinking. You can't do that with the Man in the Pub, not if you want him to buy the next round, that is. It gave me pause for thought.
The concept of NULL is one of those subjects that sorts out the sheep from the goats. Despite what you've been told it means 'Unknown'. SQL 92 finally nailed the confusion that slipped in because of its misleading name. It doesn't mean 'Nothing' . The 'propagation of nulls' is nothing more than common sense.
For example if you have an unknown quantity and add 1 to it, it is still unknown. In fact, whatever mathematical operations you do with an unknown quantity are likely to end up with an unknown result
SELECT NULL+1
/*
-----------
NULL
*/
It will be the same with strings. An unknown string, concatenated with the words 'hello world will result in a different unknown string.
SELECT 'A known value concatenated with '+NULL
/*
---------------------------------
NULL
(1 row(s) affected)*/
And, of course, SQL92 will spit on the idea of two unknowns being equal, or the result of something being compared with null to be anything other than unknown (i.e. NULL)
IF NULL = NULL
Select 'his server is not SQL-92 compliant'
ELSE
Select 'This server is SQL-92 compliant'
It is when you are doing aggregate functions that it all gets interesting.
CREATE TABLE #test (runner VARCHAR(80),Score INT)
INSERT INTO #test SELECT 'Fred',10
INSERT INTO #test SELECT 'Dave',4
INSERT INTO #test SELECT 'Simon',5
INSERT INTO #test SELECT 'Brian',11
INSERT INTO #test SELECT 'Neil',2
INSERT INTO #test SELECT 'Lionel',6
INSERT INTO #test SELECT 'Bart',7
INSERT INTO #test SELECT 'Helen',9
INSERT INTO #test SELECT 'Kathryn',5
INSERT INTO #test SELECT 'Richard',0
SELECT AVG(score) FROM #test
/*
-----------
5
(1 row(s) affected)
*/
SELECT SUM(Score) FROM #test
/*
-----------
59
(1 row(s) affected)
*/
UPDATE #test SET score=NULL WHERE runner='richard'
SELECT AVG(score) FROM #test
/*
-----------
6
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)*/
SELECT SUM(score) FROM #test
/*
-----------
59
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
*/
Now, you can see what is happening here. The row with an unknown score is being removed from the calculation so that the sum is divided by the nine known scores rather then the ten. The sum is the sum of all non-null rows.
Well, the sum of a column with a null value in it is surely NULL. so the average is as well. Instead, SQL Server assumes you've made a mistake and whisks out the row with the null value in it.
There is only one thing to be done with this sort of contradiction, and that is to avoid doing any aggregate operations on rows which have a NULL in them.
The behaviour of NULL, especially with NULLIF or COALESCE allows you to do all sorts of tricks in SQL, which are complicated to achieve any other way. Just as long as you remember that, in SQL 92, NULL means Unknown, not Nothing!