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 12345»»»

The Logic, Mathematics, and Utility of NULLs Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 9:09 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
These questions will be an ongoing project where some of the main points will be added here. The questions will be reformulated as we learn more. This is going to be a group project where everyone's input will count.

What is a NULL? It represents an unknown. But can it also stand for "N/A"? As Jeff Moden implies below, it does not stand for "nothing" because "nothing" is a known quantity.

Where did NULLs come from anyway? The origin of NULLs is important for a thorough examination of them. Were they merely invented as an ad hoc solution for example?
Aren't the unknown values in algebra (like x and y), NULLs before they're solved? If we say yes, then "unknown" quantities have been in use in mathematics at least as long as algebra has been around. So the term "NULL" from Codd (in respect to RDBMS) may be new, but the basic concept may not be - at least not in mathematics in general.

How are they used in Mathematics?

How are they used in Logic?
Over 2,000 years ago, Socrates appreciated the idea of NULL in its broadest sense. He proclaimed that he knew nothing. He was a breathing, talking NULL. But he had some terrific questions! (It's one thing to be ignorant and know it and quite another to be ignorant and not know it.)

The Utility of NULLs

Should NULLs be avoided?
Some would argue that they're a mistake from the outset. For instance, one issue that they create is they take away the "relational model" of a RDBMS in that one table can be joined to another and yet there are no rows that correspond (in an OUTER JOIN). What kind of "relation" is that? In that case, a NULL becomes a stand-in for a non-relation? Simply an ad hoc solution? (This position is coming from a previous thread, but I thought it was a persuasive argument.)

-David Portas-
According to David Portas, NULLs do not behave like "unknown values" and this is an issue. If NULLs were applied consistently in SQL, then they would be less of a problem.

But this view is more of an argument about how NULLs are used by the RDBMS rather than NULLs themselves.


Would creating different Kinds of NULLs be worthwhile? For example, maybe "N/A" should be distinguished from NULL.
Some would argue that a NULL already means that no data is available, so there is really nothing to add. Why complicate matters further? If the data is not there, it is not there - so what is it good for anyway? From a practical standpoint, there is no need to complicate the options.

NULLs can have differing meanings, depending upon how they are used and in what context. How can we easily document them to prevent confusion? [Hit tip to Steve Jones on this question.]

Some of this has been discussed in another thread, we can take some of that and add it here.

My position is subject to change, but thus far, I will argue that sometimes NULLs are useful, sometimes they're just an obstacle and can cause errors. (Yes, it depends.)


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #970493
Posted Tuesday, August 17, 2010 1:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 7,874, Visits: 9,613
Various parts of mathematics use a NULL-like concept. The ones which to me seem to be the closest to the NULL of relational databases crop up in Chris Strachey and Dana Scott's denotational semantics for programmes where one can have values like "bottom of D" for each domain D, and of course "bottom" for "bottom of everything". Basically "bottom of D" is a value in D of which we know nothing but that it is in D, while "bottom" is a value of which we know nothing, not even what domain it is a member of. Essentially all values exist in a lattice, and each domain is a sublattice, with the < relation in the lattice meaning "is less well defined". There is also a top value, which is overdefined (ie is contradictory), and potentially "top of D" values which are also overdefined but slightly less so (non-contradictory information on which domain they are in is available). Of course bottom and top occur naturally in lattice theory, even before it is applied to the denotational semantics of programmmes. But if one considers the natural topology of the lattices used in denotational semantics you see that the meaning of a recursive programme is the limit of the meanings of its partial evaluations (eg the meaning of a programme that computes factorials is the limit of the series of meanings of the programmes F(N) that produce factorials for numbers up to N but don't deliver a result for numbers > N, as N increases), which is quite a strong constraint on the lattices.

Despite the closeness of the NULLs of RDBMS with the bottoms of Scott-Strachey semantics, and the fact that both indicate an absence of knowledge about a value, they are conceptually quite different.

edit:correct the English


Tom
Post #970696
Posted Tuesday, August 17, 2010 4:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 4:49 PM
Points: 866, Visits: 7,478
WOW, over my head on the first post. OK, I'll just grab the popcorn and sit back and absorb.

Carry on.




And then again, I might be wrong ...
David Webb
Post #970803
Posted Tuesday, August 17, 2010 8:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
David Webb-200187 (8/17/2010)
WOW, over my head on the first post. OK, I'll just grab the popcorn and sit back and absorb.

Carry on.


Heh... I'll summarize. In fact, it's already on a T-Shirt... "NULLs don't mean 'Nothing'". At least not in an RDBMS.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #970836
Posted Tuesday, August 17, 2010 9:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 4:49 PM
Points: 866, Visits: 7,478
Yep, that pretty well sums it up. I've been reading Hugh Darwen for a couple of days and working through Tutorial D and the Rel implementation. 6th normal form brings its own bag of headaches, and while I tend to avoid NULLs if I can help it, I'm not sure which set of headaches I'd prefer to deal with, if given an either-or choice.

I'm a little surprised that this thread hasn't generated more traffic. It could be that everyone has already staked out their positions and they feel no need to re-defend them.




And then again, I might be wrong ...
David Webb
Post #970856
Posted Wednesday, August 18, 2010 6:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:05 PM
Points: 31,284, Visits: 15,750
NULL seems like a variable to me, in an equation that can't be currently solved. Or maybe like "i". We know that it has some value, but we cannot pick a value to state, so we use a placeholder.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #971083
Posted Wednesday, August 18, 2010 8:25 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
Steve Jones - Editor (8/18/2010)
NULL seems like a variable to me, in an equation that can't be currently solved. Or maybe like "i". We know that it has some value, but we cannot pick a value to state, so we use a placeholder.


I like this thought Steve, but I would also add that a NULL can represent an "N/A." For instance, if we have a table listing car models, then we might have a column for MPG. But what if the model is an electric car? What would we put in the column for MPG? Would it be wrong to use a NULL there since there really is no quantity that applies? We can't use "N/A" since it is a numeric field.


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #971175
Posted Wednesday, August 18, 2010 8:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:05 PM
Points: 31,284, Visits: 15,750
It could be N/A, but that implies a specific meaning. Using NULL there as a placeholder works, but you are assigning a value to NULL. I prefer to keep it as "unknown" as in there will be a ship date, but right now it's unknown.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #971201
Posted Wednesday, August 18, 2010 9:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
Steve Jones - Editor (8/18/2010)
It could be N/A, but that implies a specific meaning. Using NULL there as a placeholder works, but you are assigning a value to NULL. I prefer to keep it as "unknown" as in there will be a ship date, but right now it's unknown.


Good point - if we use NULL for miles per gallon for an electric car, it doesn't really mean "unknown" in that case, but "N/A" since the MPG doesn't even apply in this case. We're already seeing inconsistencies here. So what would all of you say to do in this case? Use a NULL anyway since it "works?" I would probably use the NULL there if it was a numeric field and couldn't change the field's type easily. But aren't we already bending the rules of NULLs? We can redefine a NULL on a whim?


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #971210
Posted Wednesday, August 18, 2010 9:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:05 PM
Points: 31,284, Visits: 15,750
If you can be sure that it will always mean N/A, you could use it. Alternatively, you could use a code, something like 99989 to mean electric car.

It's how you define it, and how you handle it in the application. However, be sure it's documented somehow.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #971213
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse