tag:blogger.com,1999:blog-13265058.post116579306321049942..comments2023-10-28T23:33:56.980+11:00Comments on Sydney Oracle Lab: NULL is my middle nameSydOraclehttp://www.blogger.com/profile/08828771074492585943noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-13265058.post-35237647890482068462007-01-19T14:23:00.000+11:002007-01-19T14:23:00.000+11:00"You have misunderstood the concept of NULLs."
No,..."You have misunderstood the concept of NULLs."<br />No, we just disagree about the concept of nulls. Your opinion is that they represent 'unknown'. My opinion is they represent a non-existence of data.<br />I find my opinion makes it easier to comprehend how Oracle deals with NULLs. I don't get paid to care about the theory of NULLs.<br /><br />Am I 100% happy with the way that Oracle deals with NULLs ? No. There are inconsistencies such as using || with a null doesn't result in a null, unlike practically every other function.<br /><br />Do I trust a database with NULLs ? Yes, I use it every day and the same functions with the same values work the same way.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-14392726330109762442007-01-19T01:43:00.000+11:002007-01-19T01:43:00.000+11:00Hi, Gary noname Myers,
Tony Andrews is right.
Yo...Hi, Gary noname Myers,<br /><br />Tony Andrews is right.<br /><br />You have misunderstood the concept of NULLs. If you read the article you linked to (it was written by me :-), you will clearly see what kind of problems you may enter into (ref. your count example).<br /><br />That article is just one of a batch: May I suggest you read the whole section:<br /><br />http://www.databasedesign-resource.com/database-theory-and-practice.html<br /><br />All these articles describe problems coming from ignorance, bypassing theory, and in some cases, complete stupidity/marketing arguments.<br /><br />Feel free to take on them in your blog, anf best of luck to you :-)<br /><br />AlfAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1166596090948088632006-12-20T17:28:00.000+11:002006-12-20T17:28:00.000+11:00"Anyway, Date's solution would generally be to sim..."Anyway, Date's solution would generally be to simply create a table of middle names, and create relationships between people and middle names."<BR/><BR/>The purely relational solution works fine for data storage. However the problem is the SQL language mechanism for picking up a parent plus an optional child is through an outer join, which results in NULLs.<BR/><BR/>Some will view this as a problem with SQL as an implementation of relational database theory (and it is). However for us working with current RDBMSs, we need to recognise that abolishing NULLs in the storage layer doesn't help at the levels of the query, application or business.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1166027534599920152006-12-14T03:32:00.000+11:002006-12-14T03:32:00.000+11:00This is no different from a "maiden name" column.Y...This is no different from a "maiden name" column.<BR/><BR/>Your middle name is known. It's nothing. NULL might suggest you have a middle name, but it's not known. <BR/><BR/>How about a numeric field indicating the number of middle names? Of course then, what about the situation where you don't know how many middle names someone has? What numeric value would you use then? You guessed it ... NULL! :)<BR/><BR/>Anyway, Date's solution would generally be to simply create a table of middle names, and create relationships between people and middle names. For you, there simply would be no such relationship. For others, there would be many ordered relationships.Robert Vollmanhttps://www.blogger.com/profile/08275044623767553681noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1166012123312688672006-12-13T23:15:00.000+11:002006-12-13T23:15:00.000+11:00You're right, NULLs are a business issue - along w...You're right, NULLs are a business issue - along with all data modelling issues.<BR/><BR/>In the relational model, there is no such thing as "absence of value" - every attribute of every tuple must have a value taken from the applicable domain.<BR/><BR/>Basically, you give a good example of the common practice of trying to shoehorn what are (in reality) two types of entities into one relation, indicated by the fact that a "nullable" field is not unknown, but inapplicable.<BR/><BR/>As you mentioned - we need a second table. I've seen cases where creating multiple tables with 1:1 relationships with each other simplifies the overall design, improves data integrity, and even improves performance.Jeffrey Kemphttps://www.blogger.com/profile/08514743151986599227noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1165886356421539162006-12-12T12:19:00.000+11:002006-12-12T12:19:00.000+11:00Not so.Firstly, "strings" are a computer concept n...Not so.<BR/>Firstly, "strings" are a computer concept not a real-world one. In the real world, I do not have a middle name. Alternatively phrased, the number of "middle" names I have is zero (and the total number of names I have is two, a Christian name and a surname). Some people have several middle names.<BR/>In purely relational terms, if names were fully normalised, they would be in a child relationship with some form of sequence.<BR/><BR/>NULL as an SQL concept is a 'shortcut' for an optional parent-child relationship where there's a maximum of one 'child' for a parent. <BR/>If you recognise that shortcut, most of the functionality around NULL makes sense. If the shortcut was removed, relational database pedants would be happier. However businesses would still have to deal with 'missing' data.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1165843352895671452006-12-12T00:22:00.000+11:002006-12-12T00:22:00.000+11:00Your middle name isn't NULL, it is '' (the empty s...Your middle name isn't NULL, it is '' (the empty string). Unfortunately, Oracle isn't smart enough to know the difference! '' is to strings as zero is to numbers, whereas NULL is just ... NULL.Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.com