April 13, 2010 at 8:56 am
Thanks Hugo! Great explanation.
April 13, 2010 at 8:57 am
NULL = "My head spinning". :w00t: :hehe::-P 😉
Seriously though, I got this one right because I assumed that the questioner was going to pick "Unknown". I thought about the question for a couple of minutes and knew the answer would either be NULL or Unknown and also knew that there would be a discussion after words with people from the opposite camp of thinking debating the answer.
I especially like Hugo's explanation.
I love the discusions for QOD and the articles. I learn more from the discussions than from the original post.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 13, 2010 at 9:02 am
This was a very poorly-worded question, and the answers weren't any better.
By definition, "NULL" means "NULL". They are not equal in a comparison, but that is what NULL means.
NULL does not mean "Unknown". It means "Not set to a value".
If I have a date column that contains NULL, then I know that the event corresponding to that column has not yet occurred. This is very meaningful information.
April 13, 2010 at 9:48 am
Speaking of tangents (sort of)...
I understand logically that NULL (as an unknown quantity) added or used in any other mathematical formula should = NULL.
But I still wish that SQL would use it as a zero since in the real world when we add 5 + NULL we want to see 5, not NULL. How often is NULL actually used as it is intended anyway in this context?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
April 13, 2010 at 10:23 am
mtillman-921105 (4/13/2010)
Speaking of tangents (sort of)...I understand logically that NULL (as an unknown quantity) added or used in any other mathematical formula should = NULL.
But I still wish that SQL would use it as a zero since in the real world when we add 5 + NULL we want to see 5, not NULL. How often is NULL actually used as it is intended anyway in this context?
There are other times when you need to know if the other value has not been set. The code should check for null in both before doing the math and doing something if one ot both are null. You can set them to zero if you want at that point. To automatically use null as zero would be dangerous, I think.
April 13, 2010 at 10:42 am
Errr, how do I get my point back? NULL does not mean anything. "Absence of any value" - hence it cannot mean "unknown." The answer choice "NULL" was the only one close to being correct.
April 13, 2010 at 10:45 am
Thank you, Pradyothana. Although by now you may be reeling from the "bad question" comments, I'd like to thank you for submitting this one. So many have complained about its amgbiguity, but that's exactly what spurred a very valuable discussion. Without your submission, we would not have been treated to the finely detailed and well-stated expositions on NULL vs. UNKNOWN, and how these terms may have different shadings of meaning in information theory as opposed to a practical application such as Microsoft SQL Server.
Bottom line is, don't be discouraged by the spirited discussion; do consider submitting future QODs.
April 13, 2010 at 11:05 am
johnf_amic (4/13/2010)
...NULL does not mean "Unknown". It means "Not set to a value".
If I have a date column that contains NULL, then I know that the event corresponding to that column has not yet occurred. This is very meaningful information.
The date may have been set at one point then set to NULL later on. This all depends on how the code handles nulls and why great caution needs to be taken with NULLs.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 13, 2010 at 11:15 am
NULL means NULL is the best answer, but the discussion is the best part of the QOTD. Great explanations, especially Hugo. Good try at the question, submitter. Way to stimulate conversation 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 13, 2010 at 11:45 am
mtillman-921105 (4/13/2010)
But I still wish that SQL would use it as a zero since in the real world when we add 5 + NULL we want to see 5, not NULL.
I don't know what "real world" you are in, but I do know that your "we" does not include me.
If I want to store something that, when added to 5, yields 5, I'll store 0. I don't need NULL for that. I use NULL as it is intended - for marking the absence of a value. Or rather, since values represents facts about entities in the real world, the absence of a specific fact about a specific entity.
And if you think that 5 + the number of my children is 5, be my guest - but I prefer my database to admit to me that it can't calculate the outcome of that sum.
April 13, 2010 at 11:58 am
Hugo Kornelis (4/13/2010)
mtillman-921105 (4/13/2010)
But I still wish that SQL would use it as a zero since in the real world when we add 5 + NULL we want to see 5, not NULL.I don't know what "real world" you are in, but I do know that your "we" does not include me.
+1
I think of NULLs like so:
Q: How tall was the person that robbed the bank?
Now, the answers could be:
A: 60 inches. (This is a known value and stored as such.)
A: I don't remember/know. (Do not use NULL for this response, because "I don't remember" has meaning.)
Only in the case the question was not asked could a NULL be placed in the answer column, depending on the structure of the DB. (In some cases, there would be a record absent if the answers represented rows.)
That's pretty much how I try to implement NULLs. A NULL 'value' in my designs means essentially missing data. We can not make decisions around the presense of said missing data; if we could, then an explicit value should be stored instead and a NOT NULL constraint should be enforced.
April 13, 2010 at 12:29 pm
Wonderful discussion. Thanks.
April 13, 2010 at 12:29 pm
Hugo Kornelis (4/13/2010)
mtillman-921105 (4/13/2010)
But I still wish that SQL would use it as a zero since in the real world when we add 5 + NULL we want to see 5, not NULL.I don't know what "real world" you are in, but I do know that your "we" does not include me.
If I want to store something that, when added to 5, yields 5, I'll store 0. I don't need NULL for that. I use NULL as it is intended - for marking the absence of a value. Or rather, since values represents facts about entities in the real world, the absence of a specific fact about a specific entity.
And if you think that 5 + the number of my children is 5, be my guest - but I prefer my database to admit to me that it can't calculate the outcome of that sum.
OK, fair enough. Then how do you handle the nulls in your numeric fields where you want to add and you want the nulls to = 0? I end up putting a "Denullify" everywhere e.g., SELECT x = ISNULL(x, 0). It works, but it gets old to do that for most every numeric column.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
April 13, 2010 at 12:42 pm
mtillman-921105 (4/13/2010) OK, fair enough. Then how do you handle the nulls in your numeric fields where you want to add and you want the nulls to = 0? I end up putting a "Denullify" everywhere e.g., SELECT x = ISNULL(x, 0). It works, but it gets old to do that for most every numeric column.
Well, you can:
- choose not to allow NULLs in the column and/or use a default value
- COALESCE the NULLs, knowing that whatever value you choose will skew aggregations, like AVG().
If a column does not have a value and you want to find an average of all values, how can you rightly interpret what the value of the NULL should be, when it was missing in the first place?
If your rules state that a NULL should really be a zero in your column, then it should be modeled and enforced that way.
April 13, 2010 at 1:10 pm
Phil Brammer (4/13/2010)
mtillman-921105 (4/13/2010) OK, fair enough. Then how do you handle the nulls in your numeric fields where you want to add and you want the nulls to = 0? I end up putting a "Denullify" everywhere e.g., SELECT x = ISNULL(x, 0). It works, but it gets old to do that for most every numeric column.
Well, you can:
- choose not to allow NULLs in the column and/or use a default value
- COALESCE the NULLs, knowing that whatever value you choose will skew aggregations, like AVG().
If a column does not have a value and you want to find an average of all values, how can you rightly interpret what the value of the NULL should be, when it was missing in the first place?
If your rules state that a NULL should really be a zero in your column, then it should be modeled and enforced that way.
Choosing not to allow NULLs in a column has proved more of a headache than it's worth - at least to me.
In fact, hats off to anyone who is using NULLs the way they're intended because I've been in working with databases for over a decade in different places and in various systems and have yet to see them implemented in a beneficial way more than once or twice in all that time. Most of what I've seen is turning NULLs into zeros.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply