Re: WHERE != and NULLS

>Date: Mon, 26 Aug 1996 13:58:31 -0400 (EDT) >From: Peter Wages <pmwages@cais.com> >Subject: Re Wher != and NULLS >X-Informix-List-Id: <list.11154> > > Thanks Jonathan and Jack - > > Doesn't NULL mean the absence of value. That is different from >unknown. It depends on who you speak to. CJ Date has no patience with nulls in any shape or form. EF Codd wants at least two different versions of 'marks', I-Marks for 'missing and inapplicable' and A-Marks for 'Missing and applicable'. SQL only has a single NULL, but doesn't define what it means. One of the reasons CJ Date doesn't like nulls is because you end up with an infinite number of them if you aren't careful -- for example, if you have I-Marks and A-Makrs, you might also need a U-Mark to indicate 'Missing and not known whether it is applicable or inapplicable'. >Unknown implies that a field has a value, except nobody knows what it is. This is an A-Mark in Codd's terminology. >Absence of value means that something definitely has no value. This is an I-Mark in Codd'd terminology. Which of these is represented by an SQL NULL is not clear; probably neither, though it is closer to an A-Mark than an I-Mark. >To say something is unknown involves religion and GOD. Errr... OK, if you say so. >Databases don't get into that sort of stuff. They do, and it gets controversial. In an attempt to avoid religious arguments, it suffices to say that the rules of ANSI SQL, and of Informix SQL, are that if a column contains NULL instead of a value, then any simple comparison (other than IS [NOT] NULL) involving that column produces unknown, and the WHERE clause of a SELECT statement (or DELETE or UPDATE) only chooses rows for which the condition evaluates to true, thereby excluding rows for which the column evaluates to false or unknown. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: References: EF Codd, The Relational Model for Database Management Version 2, Addison-Wesley, 1990. CJ Date, Relational Database: Writings 1989-1991, Addison-Wesley, 1992.