RE: Where Not In with unexpected results

Thanks to Scott for suggestion, however, no nulls exist. If nulls had existed in the database, it should not have mattered since any rows not in the table table2 should have been returned regardless. This would suggest a bug in the Informix engine. Anymore suggestions are more than welcome. In article <66pn39$jq4@cssun.mathcs.emory.edu>, sblack@elsouth.com (Scott Black) wrote: > > This seems like a similar post from a while back. I'd be willing to bet > that you have null rows in table2. Add 'and colb is not null' to your > sub-query and I bet it works. > > } -----Original Message----- > } From: danielk@vfc.com [SMTP:danielk@vfc.com] > } Sent: Thursday, December 11, 1997 10:34 AM > } To: informix-list@rmy.emory.edu > } Subject: Where Not In with unexpected results > } > } Using the sql statement and not returning any rows. > } > } select distinct col1 from table1 > } where col2 = 'ValueB' > } and col1 not in (select distinct colb from table2); > } > } This query returns no rows. > } > } Change the NOT IN to IN and results are two rows returned. > } > } select distinct colb from table2; > } Results: 2 rows with colb values of 'BALL' and 'ORANGE) > } select distinct col1 from table1 where col2 = 'ValueB'; > } Results: 5 Rows with col1 values of > } 'BALL','ORANGE','TRIANGLE','HAT','CAT' > } > } Is there a problem with the SQL? I (and others) would expect the > } results > } to be the three rows with col1 values of 'TRIANGL', 'HAT', and 'CAT'. > } > } -------------------==== Posted via Deja News > } ====----------------------- > } http://www.dejanews.com/ Search, Read, Post to Usenet -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet