<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Database Design
»
Relational Theory
»
The Logic, Mathematics, and Utility of NULLs
68 posts, Page 1 of 7
1
2
3
4
5
»
»»
The Logic, Mathematics, and Utility of NULLs
Rate Topic
Display Mode
Topic Options
Author
Message
mtillman-921105
mtillman-921105
Posted Tuesday, August 17, 2010 9:09 AM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
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
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, August 17, 2010 1:49 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:11 PM
Points: 7,104,
Visits: 7,168
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #970696
David Webb-CDS
David Webb-CDS
Posted Tuesday, August 17, 2010 4:46 PM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 827,
Visits: 5,704
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
Jeff Moden
Jeff Moden
Posted Tuesday, August 17, 2010 8:13 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 32,906,
Visits: 26,794
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #970836
David Webb-CDS
David Webb-CDS
Posted Tuesday, August 17, 2010 9:23 PM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 827,
Visits: 5,704
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, August 18, 2010 6:47 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433,
Visits: 13,745
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
mtillman-921105
mtillman-921105
Posted Wednesday, August 18, 2010 8:25 AM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, August 18, 2010 8:57 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433,
Visits: 13,745
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
mtillman-921105
mtillman-921105
Posted Wednesday, August 18, 2010 9:06 AM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, August 18, 2010 9:10 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433,
Visits: 13,745
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 »
68 posts, Page 1 of 7
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
