Path: utzoo!attcan!uunet!know!cs.utexas.edu!swrinde!ucsd!ucbvax!van-bc!tfic.bc.ca!clh
From: clh@tfic.bc.ca (Chris Hermansen)
Newsgroups: comp.databases
Subject: Re: Outerjoin implementation?
Message-ID: <1990Sep28.231042.5117@tfic.bc.ca>
Date: 28 Sep 90 23:10:42 GMT
References: <1990Sep20.161204.13014@tfic.bc.ca> <11034@sybase.sybase.com>
Reply-To: clh@tacitus.UUCP (Chris Hermansen)
Distribution: comp
Organization: Timberline Forest Inventory Consultants
Lines: 41

In article <11034@sybase.sybase.com> jeffl@sybase.Sybase.COM (Jeff Lichtman) writes:
>> 	select qqsv,count(*) from foo
>> 	group by qqsv
>> produces a separate line for every row in foo that has a null in qqsv...
>
>It's not supposed to.  This is one of those cases where nulls are supposed
>to act like they're equal to each other.  Does Informix SQL treat each null
>as a separate group?

Yeah, it does.  That's why I attached the disclaimer - I had no idea what the
standard said, let alone what Oracle, Ingres, you guys, etc etc did.

>
>BTW, I agree that nulls in SQL leave a lot to be desired.  I'm not convinced,
>though, that default values are adequate in place of nulls.  There's the
>problem of having to choose a default that's guaranteed never to be represented
>by known data values.  There's the problem of accidentally using a default
>value in a calculation as if it were a true value (calculations with null
>always yield null).  There's the problem of storage space - many database
>systems don't use any space on disk for null values, which can save a lot
>in sparsely populated tables.  There's the problem of naive users incorrectly
>interpreting default values as true data.  And on and on....

Well, at the risk of belaboring the point, I think that whenever the implied
semantics of a field suggest a default value, then it's a GOOD THING to use one.
For example, a field named widget_count is never (realistically) going to
have a value less than zero, so if you need an "undefined" value for it, use
-1, or -99999999, or some other nice number.  Similarly, a widget_status
value of "unknown" tells me more than NULL - with NULL, I can't decide whether
the status is genuinely unknown, or whether the system just doesn't know it.

So I don't think that defaults are necessarily adequate (in place of NULLs),
but I don't necessarily agree that NULLs tell you any more than default values
either (except in some cases like outer join).

Chris Hermansen                         Timberline Forest Inventory Consultants
Voice: 1 604 733 0731                   302 - 958 West 8th Avenue
FAX:   1 604 733 0634                   Vancouver B.C. CANADA
clh@tfic.bc.ca                          V5Z 1E5

C'est ma facon de parler.