Re: How to cope with missing values - NULLS?

"Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message news:61c84197.0304210717.5005e971_at_posting.google.com... > "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:<b7ur3i$2abi$1_at_gazette.almaden.ibm.com>...
> > "Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message
> > news:61c84197.0304181445.46842baa_at_posting.google.com...
> > > [snip] Let us say that we have
> > > defined a view as an outer join: columns col1 and col2 are "common,
> > > but col3 and col4 might be missing. Now let's query that view - we are
> > > only interested in tuples, where col4 is larger than 10: SQL gives us
> > > something like
> > >
> > > SELECT * FROM ojview WHERE col4 > 10
> > >
> > > How would you phrase such a query in your (hypothetical) relational
> > > system?
> >
> > Well col4 would not be of type say INTEGER, but of type say INTEGERuUNK
and I
> > would not expect the ">" operator to be defined for type INTEGERuUNK.

>
> Okay. But if you do so, are you not simply reintroducing the null-type
> through the back-door, giving it a new name and some slightly new
> semantics?

It's not a matter of 'slightly new semantics', there are logical differences here and as you know, logical differences are BIG differences. However I will agree with you that what I suggested does have some superficial similar with NULLs. The big difference though, is that my UNK is a value, while a SQL NULL is not a value. This means that UNK = UNK, unlike NULLs that don't equal each other.

BTW whenever I use outer joins in SQL (which is very often), I always COALESCE away the NULLS. Sometimes a value from the 'missing' column's datatype captures the meaning of an unmatched row, typical values being FALSE and 0. Othertimes I create a union type as best as SQL can allow. E.g. a union of Boolean and NOTYETOCCURED say.

Outer joins are not a good argument for NULLs.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Mon Apr 21 2003 - 23:59:23 CEST