SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Logic, Mathematics, and Utility of NULLs


The Logic, Mathematics, and Utility of NULLs

Author
Message
mtillman-921105
mtillman-921105
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 3852
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
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14202 Visits: 12197
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

David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 8586
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84865 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 8586
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61715 Visits: 19099
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
My Blog: www.voiceofthedba.com
mtillman-921105
mtillman-921105
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 3852
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61715 Visits: 19099
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
My Blog: www.voiceofthedba.com
mtillman-921105
mtillman-921105
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 3852
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61715 Visits: 19099
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
My Blog: www.voiceofthedba.com
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