Jim Lawrence
accessd at shaw.ca
Thu Mar 24 17:59:31 CST 2005
Hi All: I have always had some difficulty evaluating the subtle difference in the way data is conceived and described. For a field/column without data in it the field can hold three states/values. 1. Null - there has never been data in this field or the field has been set to null. 2. Empty - the field has had data in it before but the contents were cleared or removed. This is most likely a string field but it can be a numeric. 3. Zero - this only applies to numeric fields but has a similar scope to an 'empty' field. Many times, when referring to the fields/columns these three descriptions are used as synonymous qualifiers and confusion reigns supreme. That is my two cents worth. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francis Harvey Sent: Thursday, March 24, 2005 1:29 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Was... Update query through AccessADP...extendednow John, I question both the esteemed and silly part of your assertions. Fortunately, I don't require your esteem, and I didn't even have to start my own round table as there were already plenty of members of an existing order whose needs extend far beyond John's simple requirements. Stating the matter differently, and more accurately I am afraid than John's summary, null often needs to mean more than I don't know. Extending John's state example, what if it makes a difference knowing whether the state is simply unknown at this point in time or whether it was never known. This could be quite important to know when making decisions like the selection of individuals to receive a survey from a million person mailing list. Given the great expense, you might rule out somebody who never provided a valid address while deciding to perform further tracing to find a correct address for an individual whose previous address has been invalidated but who had previously been willing to participate by providing a complete address. Unfortunately, John has never been able to grasp this relatively simple concept. But really what can one expect with logic like: Null = "I don't know" "I don't know" = "I don't know" "I don't know" = NULL Date and Codd would probably shudder at the gross inadequateness of this supposed tautology. Nulls from the apples table are not the same as nulls in the oranges table however you interpret their meaning. In practical terms, my approach simply equates to an avoiding an overreliance on special gimmicks like 0 acquiring a special meaning. Whenever I need to have a special detail associated with a lookup value, I simply add an additional flag and set it when appropriate. Similarly, if I needed to know a lookup value required interpretation as a null, I would add a flag for this fact as well. Unsurprisingly, this works well for both John's pedestrian purposes and the more complicated survey and research study tracking that I have to do. Personally, I find myself wishing somebody would implement Codd's maybe join when I work with nulls. It would save so much hassle. Francis R Harvey III WB 303, (301)294-3952 harveyf1 at westat.com > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] > Sent: Saturday, March 12, 2005 1:36 PM > To: dba-sqlserver at databaseadvisors.com > Subject: RE: [dba-SQLServer] Was... Update query through > Access ADP...extendednow > > > LOL. ARTHUR! I have simply started my own round table to > deal with those > who cling to simplistic ideas at the expense of reality. > > Null = "I don't know" > > "I don't know" = "I don't know" > > "I don't know" = NULL > > I am NOT, nor have I ever espoused using a ZERO (or any other > VALUE) for a > null. > > I AM and I espouse using a record that specifically says "I > don't know". > > I do so for a very specific reason, as a workaround to a > common problem; We > are trying to select a value from a combo to fill in a field. > > Suppose that you have a person who has a valid address. They live in > California. You select California as their state. Suppose > you discover > that person no longer lives at that address but you DON'T > KNOW where she > lives? What do you do? You have four choices: > > 1) Delete the person from your database > 2) Ignore the fact that the person does not live at the old > address and just > leave the old address in there. > 3) Create a "I don't know" answer in the state table. > 4) Add code to every such combo, use a UNION to get an "I > don't know" in the > combo, then add code to delete out the existing value in the > field when that > "I don't know" is selected. Can you say Mickey Mouse? > > Choice three exactly and completely correlates to, > corresponds to, replaces > a null value. Null means "I don't know". My "zeroth record" > means "I don't > know". > > Now one of our esteemed colleagues made some silly claim > several years ago > that null could mean many different things, such as "I never got the > information" or "I had to delete the information" or ... Hmmm > it was so > silly I didn't follow the logic any further. In any event, > once stored in a > table, a null means nothing more than "I don't know". > > So, Arthur of the Foolish Clan, I simply place a record in my > database that > explicitly means "I don't know". That RECORD replaces the > null value. If > you need to explicitly denote "I don't know" when asked what > color eyes, > what state, what model car, what ... (insert your favorite > question / lookup > table here) simply select the "I don't know" answer from the > required table. > > Where your confusion comes from is the fact that I insert > this record as the > "zeroth" value in the PK field. I am NOT equating a zero to > NULL, I am > creating an "I don't know" record at PKID value zero! > > I do that as a convenience. Access (and SQL Server IIRC) start their > identity value with 1 and work up if it is a linear ID. That leaves a > "hole" at the zero value where I can insert a record. I > literally append a > record into the table with a ZERO for the PK (I always use an > autonumber as > the PK) and fill in "I don't know" for the value (if the > value displayed is > a piece of text). If having a null is so danged important to > you, then > insert a null in the value. I find that having the text "I > don't know" > tells the user that this is the "I don't know" record but you > are the master > of your databases. > > Nulls stored in PK values can work to your advantage, or be a > royal PITA. > Nulls cause inner joins on that field to fail to pull a > record. If you want > to pull a value you now have to (remember to) use an outer > join. That's > fine if you KNOW that there will be nulls but it is just a > PITA to me. I > have to assume that you use outer joins everywhere since any > column may have > nulls in them? > > At any rate, you can see that I do NOT equate a zero to a > null. I know > perfectly well that a null is not a zero, zeros have a > specific meaning and > NULLS have a specific meaning. And I understand joins > perfectly well thank > you. > > I am NOT going to apply for membership back in to your round > table! I have > my OWN round table thank you. > > ;-) > > John W. Colby > www.ColbyConsulting.com > > Contribute your unused CPU cycles to a good cause: > http://folding.stanford.edu/ <snip> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com