on NULLs (was RE: Counterquestion - is Oracle a He or a She - or an It or a hermaphrodite)

Didn't want to sound like I'm making fun of someone's English, so I changed the subject.

There are two issues to keep in mine here: 1) how do you represent absence of value in your application 2) NULL is not a value, it's an absence of one

The second one is a technical one, and ultimately an abstract one, dealing with the inner workings of the database engine. The first one is a practical one, dealing with the needs that your application has to meet. The first one comes before the second one in my view, because you can in fact create a usable application that doesn't utilize NULL columns.

It can be useful to think about the issue by answering this question first:

How would you get rid of NULL columns?

1. You could write an application where each table consisted of the primary key and only one attribute column. Then you would use left outer joins to determine whether a value exists or not. If you didn't have anything to put as the value for an attribute, you simply wouldn't insert the row into the attribute's table.
2. Another way of doing it, and the way it's done in databases that don't support NULL (or where the developer doesn't know what NULL is and how to use it) is for each attribute to come up with a value that would stand for UNKNOWN. It's easy to do with strings ('UNKNOWN'), fairly easy with dates (1500-01-01 for timestamp field and 9999-01-01 for date-of-birth field) and harder still with numbers (999999999 for UNKNOWN number of livestock on a farm is reasonable, but 999999999 for UNKNOWN amount in a bank account is probably not).

Both a) and b) have their drawbacks: performance, space wastage and query complexity for a) and likelihood of introducing ambiguity and space wastage for b). There are probably others that I can't think of right now.

NULL is basically a special value (specific to your database engine, _not_ to your application) that means "UNKNOWN" or "DOESN'T EXIST" and it works equally well for all data types, but is especially useful for numbers. Yet using NULL has its drawbacks too: learning curve, performance issues in some situations, and discussions such as the one we are having :)

Max.

--
http://www.freelists.org/webpage/oracle-l