Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

NULL Expand / Collapse
Author
Message
Posted Friday, April 16, 2010 2:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 22, 2011 12:47 PM
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.
Post #905201
Posted Wednesday, May 5, 2010 11:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 5, 2010 11:26 AM
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.

Post #916310
Posted Friday, July 23, 2010 3:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 9:52 PM
Points: 125, Visits: 29
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
Post #957841
Posted Friday, July 23, 2010 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,851, Visits: 9,602
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
Post #957875
Posted Friday, July 23, 2010 2:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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)

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
Post #958287
Posted Saturday, July 24, 2010 2:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,851, Visits: 9,602
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
Post #958429
Posted Sunday, July 25, 2010 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #958466
Posted Monday, November 1, 2010 3:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:10 AM
Points: 1,370, Visits: 506
My point of view 'does mean' and 'does equal to' aren't the same. NULL mean NULL, but equals to UNKNOWN. Bad question.
Post #1013723
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse