Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: So what's null then if it's not nothing?

Re: So what's null then if it's not nothing?

From: David Cressey <>
Date: Wed, 23 Nov 2005 04:11:32 GMT
Message-ID: <UZRgf.4440$>

"Hugo Kornelis" <hugo_at_pe_NO_rFact.in_SPAM_fo> wrote in message

> Yeah - but you'll often want to combine data from various columns; if
> this design has scattered these columns over various tables, you'll have
> to join them together. Inner joins would lose you your relevant data, so
> you'll have to use outer joins -- and that means that the nulls you
> worked so hard to get rid of will be looking you straight in the face
> again.
> >2- You create a parent/root entity/table and make a subtype for each
> >optional field
> And that would lead to an exponential explosion of tables. With one
> optional attribute, you get two subtypes. Two optional attributes means
> four subtypes. With 10 optional attributes, you'd need no less than
> 1,024 tables. And for a simple aggregation, you'd have to UNION half of
> them together.
> It might not be an argument for a theory group, but the performance of
> such a monster would be awful.

Pardon me for being dense, but is your point above not the same as the point I was making earlier
when I said that tables are necessarily rectangular in form, but available (and/or applicable) data is not always rectangular in form?

In an earlier discussion of the same topic, I said that every NULL can be construed as the result of an outer join. Another way of saying the same thing is to say that every table that contains an optional column can be understood as having materialized an outer join between a table that contains only required columns, and a table that contains the same columns
plus the optional column.

That doesn't mean that the above would be sound design. It's cheaper, better, more coherent, more practical, and just as theoretically sound to include a column that permits NULL.

I *think* you and I are on the same page in this matter. Am I right? Received on Tue Nov 22 2005 - 22:11:32 CST

Original text of this message