Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!van-bc!tfic.bc.ca!clh From: clh@tfic.bc.ca (Chris Hermansen) Newsgroups: comp.databases Subject: Re: Outerjoin implementation? Message-ID: <1990Sep28.211738.4855@tfic.bc.ca> Date: 28 Sep 90 21:17:38 GMT References: <5803@plains.NoDak.edu> <1990Sep20.161204.13014@tfic.bc.ca> Reply-To: clh@tacitus.UUCP (Chris Hermansen) Distribution: comp Organization: Timberline Forest Inventory Consultants Lines: 63 In article cimshop!davidm@uunet.UU.NET (David S. Masterson) writes: >In article <1990Sep20.161204.13014@tfic.bc.ca> clh@tfic.bc.ca >(Chris Hermansen) writes: > [my examples deleted...] > >Use of these statements depend on the definition of the data. I would hope >that the "casual user" would understand the data he is dealing with. For >instance, in the above statements, it would seem that qqsv might be the >primary key of foo, in which case, it can never be NULL, so all 3 statements >degenerate into the first. If its not the primary key, then there must be a >reason for qqsv to be NULL and (most likely) a completely different reason why >qqsv might be 0 which would influence the use of "IS NULL". Sorry, I guess I didn't make myself clear enough. It's not the fact that NULL is different than 0 (I think this is neat, as I'm sure most people do) that bothers me. It's the (IMHO) weird three-state logical semantic nausea that hangs around NULLs. What I mean by that is that NULL values occurring in arithmetic or logical expressions cause those expressions to behave differently than `normal' values. One gets into `extra' testing and such that doesn't happen if a non-NULL value is used to represent the absence of a realistic value > > 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? > >I don't have a database handy, but I think it also produces a separate line >for every row in foo that has a 1 in qqsv, so what's your point? There's no >substitute for an intelligent query. No, no, no! The whole point of `group by qqsv' implies that you get ONE row for each value of qqsv; the exception here is that rows containing NULL in qqsv do not group together because neither NULL = NULL nor NULL <> NULL is true. My rather trite example could normally be used to get a count of the number of rows with different values of qqsv, but as you say, a more intelligent solution is required when there are NULLs lurking about: select qqsv,count(*) from foo where qqsv isnt null group by qqsv; select count(*) from foo where qqsv is null Just to rant and rave for a moment; suppose that a careful programmer sets up a system of forms and reports, the forms carefully ensuring that the user cannot enter NULL values into fields. Suppose that two years later, a not-so-careful programmer comes along and modifies one of the forms, inadvertently allowing NULLs to be entered. Come year end, the report programs, formerly shielded from lurking NULLs, now produce a monumental pile of garbage, and the original programmer gets dumped all over because s/he "didn't allow for the occurrence of NULLs in the database". You be the judge. 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.