Re: NULLs

On Fri, 2 May 1997, Mark D. Stock wrote: > Edward A. Sarog wrote: > > Correct but there should be no unknown data in the database. > > i.e. if you don't know then find out before trying to enter it into > > MY database. :-> > That's not quite true. You mean no unknown key values. Every > database should allow for the storage of unlnown data. Are you saying > that I can't add a contact until I know their EXACT street address? More than that, a standard example: Say you maintain an employee database and the Big Boss wants to know who's adhering to dress code. You create a (not very normalized) table containing fields such as jacket_color (of course, any color other than blue would cause a stock plunge... except on Fridays.. or something..). There you go, happily inserting rows as people come to work, "Blue, blue, black, blue, grey, blu" ... all of a sudden someone comes to work [fx: gasps] WITHOUT A JACKET!!! Oh, no. You don't have a field for that. Even if you did -- what color is it? The true answer here is NULL. The question, "What color is his jacket?" simply cannot be answered. Any other entry would be incorrect. NOTE: I realize that this is a dumb example. However, I DO NOT WANT to see this turn into a debate about dress codes, normalizeation, etc. It's just an example. Another one, and another valid use of NULL: You have a table containing information (address, owner, etc) about a lot of buildings. Suddenly, you decide to store height as well. Since all buildings have one and only one height, it makes sense to add a field to the existing building_info table. The default value for that field? NULL. Why? This is a knowable value (unlike jacket color) but you don't have it yet. Now, suppose you've gone out and measured 50 of the 1000 buildings, inserting their values nicely (Well, updating). Someone asks you for the average height of the buildings. THe query: SELECT avg(height) FROM building_info Will return null. This is CORRECT. Why? Simply because you (and your table) lack sufficient information to provide an accurate answer. You could reply, "Well, the average height OF THE ONES I'VE MEASURED is..." SELECT avg(height) FROM building_info WHERE height IS NOT NULL Without NULL, you have no way to indicate the non-existent information. Just my poorly written (it was a long supporting night last night) US$0.02 -Richard