tag:blogger.com,1999:blog-13000143.post116594629678135225..comments2023-11-05T00:48:20.985-07:00Comments on Radio Free Tooting: Title is NULLAPChttp://www.blogger.com/profile/18348719053445885097noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-13000143.post-69323388670662957472007-06-21T01:20:00.000-07:002007-06-21T01:20:00.000-07:00These comments have been invaluable to me as is th...These comments have been invaluable to me as is this whole site. I thank you for your comment.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1169727485242801122007-01-25T04:18:00.000-08:002007-01-25T04:18:00.000-08:00Hi Andrew,Being the author of the article Mr. Myer...Hi Andrew,<BR/><BR/>Being the author of the article Mr. Myers used as basis for his middle name concern, I'll put my 2 cents to the discussion.<BR/><BR/>BTW: The article is here:<BR/><BR/>http://www.databasedesign-resource.com/null-values-in-a-database.html<BR/><BR/>Pay attention to the COUNT problem...<BR/><BR/>Now, we live in a world of compromises. But let's get to the very point of it:<BR/>The relational model says that tuples contain TRUE propositions. When a row (a proposition) contains one or more NULLs, we don't know if it's true anymore. Hence, we are outside the relational model. It's not more complicated than that.<BR/><BR/>What we really are saying, is that, given today's DB products (performance issues, multiple joins etc. etc.) we must live/deal with NULLs. Even Chris Date and Fabian Pascal says so (I've spoken to both of them). That doesn't make NULLs more sound, though.<BR/><BR/>I wish Mr. Myers also would read the parent section of my article:<BR/><BR/>http://www.databasedesign-resource.com/database-theory-and-practice.html<BR/><BR/>because there would possibly be even more material to blog on :-), however, it makes sense, doesn't it?<BR/><BR/>And as for the SELECT DISTINCT... <BR/><BR/>What you say, really, is that youe have two (or more) identical propositions... Is that relational? :-)<BR/><BR/>It's a hard world.<BR/><BR/>Best regards,<BR/>AlfAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1166086365363553472006-12-14T00:52:00.000-08:002006-12-14T00:52:00.000-08:00RobertWell Gary definitely refutes the idea that h...Robert<BR/><BR/>Well Gary definitely refutes the idea that his middle name is an empty string. <BR/><BR/>>> I believe Date's solution was to create a table of middle names, and a relation table to link people to their middle names. And Gary simply wouldn't have such a relationship.<BR/><BR/>That was the solution which Hugh Darwen presented and to which I alluded. The problem is sixth normal form usually ends up with a table per column. And we still have to handle the null values we get from the outer joins.<BR/><BR/>Cheers, APCAPChttps://www.blogger.com/profile/18348719053445885097noreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1166044459219490452006-12-13T13:14:00.000-08:002006-12-13T13:14:00.000-08:00Ok Tonguc, I think I didn't make myself clear, I k...Ok Tonguc, I think I didn't make myself clear, I know there are a lot of issues using NULL values in constraints, calculations, etc, but my point is that when designing a database the last option should be to allow the user to insert a NULL value. <BR/><BR/>I think an attribute should have all the possible options like 'unknown', 'not defined',etc and if the attribute is numeric an exact range of numbers, but not let the db defines what kind of NULL was inserted...Carloshttps://www.blogger.com/profile/15934070138187932218noreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1166026819105921462006-12-13T08:20:00.000-08:002006-12-13T08:20:00.000-08:00To me, NULL generally means "unknown."Gary's middl...To me, NULL generally means "unknown."<BR/><BR/>Gary's middle name is not NULL. It is not unknown. It's known. It's nothing. NULL and nothing are two entirely different things.<BR/><BR/>In a general sense, I believe Date's solution was to create a table of middle names, and a relation table to link people to their middle names. And Gary simply wouldn't have such a relationship.<BR/><BR/>NULL is definitely one of the more interesting topics, I've blogged on it three separate times (see my archives if you're interested).Robert Vollmanhttps://www.blogger.com/profile/08275044623767553681noreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1165994233833130762006-12-12T23:17:00.000-08:002006-12-12T23:17:00.000-08:00Carlos I believe the attention is because when usi...Carlos I believe the attention is because when using NULL in database there are several things to be more carefull. Allowing NULL values into your columns introduces a whole new degree of uncertainty into your database. The primary reason behind is comparing NULL to a value will result neither TRUE or FALSE. Or for example;<BR/><BR/>create table tab_null_test ( x int, y int );<BR/>create unique index unique_tab_null_test on tab_null_test(x,y);<BR/>insert into tab_null_test values ( 1, 1 );<BR/>insert into tab_null_test values ( 1, NULL );<BR/>insert into tab_null_test values ( NULL, 1 );<BR/>insert into tab_null_test values ( NULL, NULL );<BR/>analyze index unique_tab_null_test validate structure;<BR/>select name, lf_rows from index_stats;<BR/>NAME LF_ROWS<BR/>------------------------------ ----------<BR/>UNIQUE_TAB_NULL_TEST 3<BR/><BR/>The table has four rows, whereas the index only has three. The first three rows, where at least one of the index key elements was not Null, are in the index. The last row, with (NULL, NULL) is not in the index.<BR/><BR/>insert into tab_null_test values ( NULL, NULL );<BR/>1 row created.<BR/><BR/>In Oracle, (NULL, NULL) is in deed <> (NULL, NULL). That is something to consider: each unique constraint should have at least one NOT NULL column in order to be truly unique.<BR/><BR/>NULLS can also affect the outcome when you use a descending sort. By default, NULLS are greater than any other value.<BR/><BR/>Oracle implements NULL values as ANSI SQL requires them to be implemented, but databases are different, also experience in one will in part carry over to another. <BR/><BR/>Thank you for your article Andrew,<BR/>Best regards.Tonguçhttps://www.blogger.com/profile/10296956337243833265noreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1165965403286372852006-12-12T15:16:00.000-08:002006-12-12T15:16:00.000-08:00I can't understand why so much attention to NULL v...I can't understand why so much attention to NULL values. When I have to set different options like 'not known', 'undefined', etc, for an answer in a form, well I just put the options for the user to select and thats it. NULL is just NULL. ;)Carloshttps://www.blogger.com/profile/15934070138187932218noreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1165963185001923052006-12-12T14:39:00.000-08:002006-12-12T14:39:00.000-08:00With 13 different NULLs, my IS NULL now needs to b...With 13 different NULLs, my IS NULL now needs to be<BR/><BR/>col IS UNKNOWN_NULL or<BR/>col IS NEVERGOTIT_NULL or<BR/>col IS DOESNTEXIST_NULL<BR/><BR/>That would wreak havoc on some trigger code.<BR/><BR/>:-)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-13000143.post-1165950124092466922006-12-12T11:02:00.000-08:002006-12-12T11:02:00.000-08:00Perhaps we need :Select DISTINCT NULL...:-)Perhaps we need :<BR/>Select DISTINCT NULL...<BR/><BR/>:-)Pete Scotthttps://www.blogger.com/profile/17524162121927585565noreply@blogger.com