Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!sun-barr!decwrl!elroy.jpl.nasa.gov!peregrine!ccicpg!felix!asylvain From: asylvain@felix.UUCP (Alvin E. Sylvain) Newsgroups: comp.databases Subject: Re: Outerjoin implementation? (NULL values) Summary: NULL values Keywords: NULL values Message-ID: <151657@felix.UUCP> Date: 28 Sep 90 18:10:03 GMT References: <5803@plains.NoDak.edu> <1990Sep20.161204.13014@tfic.bc.ca> Sender: daemon@felix.UUCP Reply-To: asylvain@felix.UUCP (Alvin E. Sylvain) Distribution: comp Organization: FileNet Corp., Costa Mesa, CA Lines: 50 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!! [ ... ] >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 [ ... ] In Oracle, you can say: update foo set bar = 37 where nvl (qqsv, 0) != 0 Which uses the "nvl" function produce a NULL-value substitution. It basically says (in this example), if the value is NULL, use 0 instead. So you want all rows as in your second example. I'm sure that if you check the Informix manual, you'll find something similar. I can't say if this "standard" SQL, or merely Oracle's little implementaion goodie, but it's probably the easiest, cleanest way of handling that particular potential snafu. As to strange 'gotchas', any tool in existance has them, some more than others. Take C pointers for example (Yowch!). It's just another thing the tool-user must be aware and careful of. ("Hey, Charlie, don't leave your hand there! You'll cut yer silly finger off!" ;-) ------------------------------------------------------------------------ "I got protection for my | Alvin "the Chipmunk" Sylvain affections, so swing your | Natch, nobody'd be *fool* enough to have bootie in my direction!" | *my* opinions, 'ceptin' *me*, of course! -=--=--=--"BANDWIDTH?? WE DON'T NEED NO STINKING BANDWIDTH!!"--=--=--=- -- ------------------------------------------------------------------------ "I got protection for my | Alvin "the Chipmunk" Sylvain affections, so swing your | Natch, nobody'd be *fool* enough to have bootie in my direction!" | *my* opinions, 'ceptin' *me*, of course! -=--=--=--"BANDWIDTH?? WE DON'T NEED NO STINKING BANDWIDTH!!"--=--=--=-