Is NULL an appropriate value for an applicable, known non-value in a record? (i.e. the value is known to be "no value")

Wikipedia says NULL is for representing "missing information and inapplicable information".

I have always underestood NULL to be appropriate to represent "Unknown" information, as in the field is applicable to the record, it is just an unknown value.

Given the problems that NULL introduces to queries (3-point logic and tons of exc开发者_运维百科eptions that have to be accounted for) should NULL still be used in records for fields that are not applicable to a record? Or should it just be used for applicable fields whose values are unknown for a given record?

I too accept null to mean "unknown", but "inapplicable" fits for example when saving the CEO's employee record, we set employee_manager_id = null because the CEO has no manager.

One technique to avoid the hassle of nulls is to use a special value instead, for example saving -1 for an person.age, but then you have a bit more complexity checking this value. When using a special value for a foreign key (as in the manager id example), you actually have to create a dummy record with id = 0 for example, and this may introduce problems when processing "all employees" - you must skip the dummy record.

Personally, I think things stay cleaner just using null and suffering the hassle of more complex SQL - at least everyone knows what's going on.

In fact, Null does not connote 'unknown' so much as just 'no data'. It is used in SQL (and other environments) where data is simply absent for a given field.

With regard to your concern about 3-point logic and exceptions, your application is probably making use of more language than just sql. The code of your system that interfaces with SQL should be handling the question of what to do with Null fields.

If Null is simply unacceptable (i.e. you can't have your data structure without a non-null value), then you had better avoid the concepts of 'unknown' 'no data' altogether. Make the field required by setting the SQL column's Null value to false; that makes it so that Null cannot be entered as a valid value. E.g.

CREATE TABLE foo (bar INT NOT NULL);