Re: Using character data types in a sub-query

otto@alldata.com (Doug Otto) wrote: :You may want to put this one in your notes to avoid hair loss: Here goes the null issue again. There is no hair loss whatever over this. See below. ...snip table description... :>SELECT count(*) : FROM table_1 : WHERE table_1.column_1 NOT IN : (SELECT column_1 from table_2); : : : (count(*)) : : 0 : :1 row(s) retrieved. If there are null "values" returned from the subselect the definition (the ANSI standard) sais that the actual value is unknown. This means that it's unknown whether table_1.column_1 is in the set (or rather multiset) returned by the subselect. As unknown is interpreted as false in a where clause the count becomes 0. ...snip of selects showing that there are no rows in table_2 with values corresponding to table_1... :Even though the form is correct, the original query fails to produce : believable results _if_ the results of the sub-query contain null : values. I haven't confirmed it yet but this "quirk" appears to be : a problem only with non-numeric data types. I was able to confirm : that the problem still exists in 7.x. This is *not* a "quirk", but the way it should work according to the ANSI standard and probably the most reasonable interpretation of what a null in a column means. : The fix for this is forcing : your sub-query to exclude null values: This is a "fix" in that it gives the results you want in this situation. Granted it may be what one want's in most situation, but that doesn't change that the current behaviour is correct. The "fix" is even quite simple. Null is a strange animal. I have found that it helps a lot to allways remember that it's not a value. It's an indicator saying that the value is unknown (and may be anything). :___ : :>SELECT count(*) : FROM table_1 : WHERE table_1.column_1 NOT IN : (SELECT column_1 from table_2 : WHERE column_1 IS NOT null); : :~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ :Doug Otto otto@alldata.com :Alldata Corporation dotto@calweb.com :Technical Manager, Database Development (800) 829-8727 ext. 3137 Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org