Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!mit-eddie!uw-beaver!zephyr.ens.tek.com!tektronix!sequent!lugnut From: lugnut@sequent.UUCP (Don Bolton) Newsgroups: comp.databases Subject: Re: Outerjoin implementation? Message-ID: <42743@sequent.UUCP> Date: 21 Sep 90 13:52:14 GMT References: <5803@plains.NoDak.edu> <1990Sep20.161204.13014@tfic.bc.ca> Reply-To: lugnut@sequent.UUCP (Don Bolton) Distribution: comp Organization: Sequent Computer Systems, Inc Lines: 61 In article <1990Sep20.161204.13014@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: >In article cimshop!davidm@uunet.UU.NET (David S. Masterson) writes: >>In article pcg@cs.aber.ac.uk >>(Piercarlo Grandi) writes: >> >> It may be preferable to avoid the difficult issues posed by null values, > >Yay! Someone else who hates nulls!! > >> and the definition of an outer join operation, by attaching instead to >> each relation (or to the underlying domains) a default value chosen by >> the schema designer, and/or to make an outerjoin just a join where a >> suitable matching default value is automatically supplied. >> >>This method *may* obviate the need for null records that occur in outer joins, >>but it doesn't obviate the need for NULLs as attribute values. In fact, this >>is the method documented by Ingres for doing outer joins in Quel. Using >>default values, though, has always had the problem of finding a default value >>that will *never* be used as a live value. > >NULLs almost guarantee strange `gotchas', don't you think? For instance, >instead of writing a statement like > > update foo set bar = 37 where qqsv != 0 > >the SQLer has to decide if > > update foo set bar = 37 where qqsv != 0 or qqsv is null > >is what s/he REALLY means. Put another way, > > update foo set bar = 37 where qqsv != 0 and qqsv isnt null > >provides no extra degree of refinement over the first update statement, even though >it appears to (for the casual observer, at least). > >It's also really handy that > > select qqsv,count(*) from foo > group by qqsv > >produces a separate line for every row in foo that has a null in qqsv, >don't you think? > ARRGGH, I hate it when that happens, have to update the nulls with the string value "NULL" run the select and then "unupdate" or run several selects and cut and paste. Either way its messy. >Note that I use Informix SQL, so your lack of mileage may vary on the above > Also with informix, a select count(*) from foo where qqsv is null will produce a SINGLE row with the count. Can you say inconsistant? I knew you could :-) "null is an unknown value, and as such each one is unique" Always thought I had an odd sense of humor...:-) The "O" companies database groups the count in either instance. althought they profess the same quote above, at least consistancy is present.