April 16, 2010 at 2:28 pm
The correct answer to this question is incorrect.
The keyword "NULL" represents the special value NULL.
In the context of (relational database) boolean logic, there are three possible values: TRUE, FALSE and NULL. In a boolean context, NULL means NULL. The predicate (e.g.)
WHERE 1=NULL
evaluates to NULL. This is not unknown, it is very specifically known to be the special value NULL. When combined with other boolean expressions with AND and OR operators, the value of NULL is well defined, it's not "unknown".
Yes, NULL CAN be used represent the idea "the value for this expression is unknown". But it certainly is not the only meaning. A NULL value can be used to represent other concepts, such as "a value is not applicable" for a particular row, or a value is "not yet assigned", or that some event has not yet occurred. These meanings are distinct from the concept of UNKNOWN. (We can use a NULL to represent something that is known.
The assertion that NULL means UNKNOWN is incorrect. That notion is inadequate and incomplete.
May 5, 2010 at 11:29 am
Probably one of the weakest q's on QotD. NULL <> NULL doesn't translate to English AT ALL. Nice try Prad.
July 23, 2010 at 3:35 am
Very good question indeed. NULL does not mean NULL. try running the following statement. This will always return 'NOT Matched' because NULL cannot be compared to NULL as it means nothing or its unknown. 😀
SELECT CASE NULL WHEN NULL THEN 'Match' ELSE 'NOT Matched' END
July 23, 2010 at 5:05 am
Ameya- Ameyask (7/23/2010)
Very good question indeed. NULL does not mean NULL.
That erroneous view is a fairly natural consequence of treating NULL as a value which exists in all domains instead of as a marker quite separate from the domain of values indicating the absence of any value. This error was originally committed by Codd, but he corrected it later (and by 1986 was advocating two different NULL markers, one for "Inapplicable and therefore absent" and the other for "Applicable but absent", and a 4-valued logic with values T, F, I and A); unfortunately the SQL crew didn't pick up the correction, still less the idea of two different nulls, and the ANSI standard explicitly states that its single NULL is a special value. A value that denotes the absence of any value is a bit of a loopy concept - presumably it denotes the absence of itself, so it would fit well into Charles Dodgson's works of fiction but not at all well into his textbooks on mathematics and logic.
Tom
July 23, 2010 at 2:49 pm
Tom.Thomson (7/23/2010)
Ameya- Ameyask (7/23/2010)
Very good question indeed. NULL does not mean NULL.That erroneous view is a fairly natural consequence of treating NULL as a value which exists in all domains instead of as a marker quite separate from the domain of values indicating the absence of any value. This error was originally committed by Codd, but he corrected it later (and by 1986 was advocating two different NULL markers, one for "Inapplicable and therefore absent" and the other for "Applicable but absent", and a 4-valued logic with values T, F, I and A); unfortunately the SQL crew didn't pick up the correction, still less the idea of two different nulls
Hi Tom,
It is in fact A Good Thing that the SQL crew didn't pick up the correction, as it was based on an error. And you fall for the same trap that Codd fell for. This was caused by Date (a firm opponent of any NULLs in the relational model), who managed to make Codd believe that one kind of NULL is not enough - whether Date didn't see the fallacy in the argument or whether he deliberately introduced it to trap Codd, I do not know. But he did manage to get Codd to admit that there have to be two kinds of NULL, and then expanded on that to add even more kinds of NULL, bringing the whole concept of NULL ad absurdum.
The error, that Codd and you (and maybe Date) overlooked, is that including two or more different NULL markers in the relational model would violate one of its basic principles: atomicity. A single column should store a single attribute; multiple attributes combined into a single column are considered bad. And since the attributes "Birthday" and "Reason birthday is missing" are distinct, they should be represented in seperate columns (assuming both are relevant for the business). Suggested further reading: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx. (Sorry for the blatant self-promotion:-D)
and the ANSI standard explicitly states that its single NULL is a special value. A value that denotes the absence of any value is a bit of a loopy concept - presumably it denotes the absence of itself, so it would fit well into Charles Dodgson's works of fiction but not at all well into his textbooks on mathematics and logic.
The ANSI standard does not describe NULL as a value that denotes the absence of any value, but as "a special value that is used to indicate the absence of any data value". (ISO/IEC 9075-1:2003, page 5 - emphasis added by me). Not loopy at all. And to prevent this confusion, many good textbooks instead describe NULL as a marker to indicate the absence of any value.
July 24, 2010 at 2:39 pm
Hi Hugo
No need to be sorry about the self-promotion when it leads to an interesting blog entry with a useful external reference. The "much ado" paper contains several excellent examples of Date's ability to think unclearly. I wasn't aware this was available on the web.
I think we will probably continue to diagree about NULLs; Date's reductio ad infinitum argument about a system with two or more nulls is blatant nonsense. I can see how the argument that the I-NULL has semantic content outside the domain originally designated for the values can be made, but it can equally be made for the A-NULL and indeed for the single NULL of the 1979 paper (after all, the model has it explicitly added into each domain because the original domain contains no such value), so if one accepts the argument that this is a violation of atomicity or of 1NF one ends up unable to accept NULLs at all. So I'm happy with 2 distinct nulls. Of course I'm also happy (slightly less happy - but only slightly) with a single NULL, as long of course as it's recognised that it is not a value in for example the domain of integers but a marker indicating the absence of a value. I think it's possible that Codd went too far when he introduced a 4-valued logic to go with his 2 NULLs: there's nothing in a 2 NULL system that can't be handled easily in 3-valued logic (as Codd himself seems to say in the 1993 paper you referenced).
Anyway, thanks for the comment; while we may disagree, I'm perfectly happy to see people disagree with me as long as (like you, but unlike Date on the topic of NULLs or some people who have taken him too seriously) they have coherent and respectable arguments for the positions they take.
Tom
July 25, 2010 at 1:58 am
Hi Tom,
I don't know the 1979 paper (never been able to find it online), but I don't think it matters much. In my opinion, academics tend to argue so much about terminology that they never realise that they actually agree about the underlying concepts.
If you want your columns to be atomic, then a "Birthday" column can only hold a valid date that is the birthday of the represented entity - or nothing at all if (for whatever reason), the birthday of that entity is not recorded in the database. The "nothing at all" needs some representation. In a pen and paper table, that would be an empty cell or a dash (to indicate the author did not forget to fill the cell, but left it blank intentionally). In a relational database, the representation for "nothing at all" is called NULL (and the actual bits-and-bytes representation is left to the vendor). Whether you add NULL as a "special value" into each domain, or calll NULL a "marker" that is not part of any domain is just semantics - as long as we agree that the only information carried by NULL is "nothing here", we are in full agreement.
November 1, 2010 at 3:32 am
My point of view 'does mean' and 'does equal to' aren't the same. NULL mean NULL, but equals to UNKNOWN. Bad question.
Viewing 8 posts - 61 through 67 (of 67 total)
You must be logged in to reply to this topic. Login to reply