Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NULL


NULL

Author
Message
spencerpnhcc-alt
spencerpnhcc-alt
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 49
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.
gsjohnson
gsjohnson
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 18
Probably one of the weakest q's on QotD. NULL <> NULL doesn't translate to English AT ALL. Nice try Prad.
Ameya- Ameyask
Ameya- Ameyask
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 30
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. :-D

SELECT CASE NULL WHEN NULL THEN 'Match' ELSE 'NOT Matched' END
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10753 Visits: 12019
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

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10753 Visits: 12019
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

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
palotaiarpad
palotaiarpad
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 753
My point of view 'does mean' and 'does equal to' aren't the same. NULL mean NULL, but equals to UNKNOWN. Bad question.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search