Re: Joins with nulls

"David Cressey" <david_at_dcressey.com> wrote in message news:<zF9D9.285$0I3.29569_at_petpeeve.ziplink.net>...
> > Nulls are not values, are a kind of mark.
>
> Add to what you said, that nulls are themselves the result of outer joins
> on the underlying data.

I don't see your point very well.

> A relational model of the data will not have any nulls in it.

Of course, but I prefer to use here design instead of model because it is more clear.

> Optional
> columns in tabulated data, (SQL style data)
> are a consequence of denormalizing the relational model,

IMO are a consequence of broking the relational model because a relation can't have nulls. A relation with nulls is not a truly relation.

Thus an outer join operation that returns a relation with nulls is not a truly relational operation.

The Third Manifesto book proposes an outer join operator with a fill clause, that does not involve nulls (see page 220).

> Take a column like "Middle Initial" in a table that's describing persons.
> If you really want to avoid NULLS, and you don't want to cheat, you need a
> table that contains two attributes (Person_ID, Middle_Initial).

Yes, but in this case you have alternatives, you can use the empty string value. An empty string is not a null, is a value.

Some SQL DBMSs does not permit nulls in character attributes because you can use the empty string for representing missing information.

> However, I DON'T recommend this as a practical solution in, say, a payroll
> system.

I agree. With actual DBMSs the empty string solution is usually better, but it is due to actual DBMSs faults.

> I recommend an optional column, as disgusting as this may seem to
> the theorists.

IMO what disgusts theorists is that with actual products you don't have many options for manipulating the physical design of the database.

For instance the two logical tables you proposed could be mapped to one physical table and perform as well as the design with only one logical table.

Alfredo Received on Fri Nov 22 2002 - 16:00:23 CET