Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 75BC963270F for ; Wed, 28 Jan 2009 14:05:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 48972-01 for ; Wed, 28 Jan 2009 14:05:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 693166325F7 for ; Wed, 28 Jan 2009 14:05:32 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 28 Jan 2009 12:05:29 -0600 Message-Id: <49804A00.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.3 Date: Wed, 28 Jan 2009 12:05:20 -0600 From: "Kevin Grittner" To: "Scott Whitney" , Subject: Re: Strange query problem... References: <20090128174824.B4C457E4165@mail.int.journyx.com> In-Reply-To: <20090128174824.B4C457E4165@mail.int.journyx.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200901/149 X-Sequence-Number: 31196 >>> "Scott Whitney" wrote: > Um. How is this possible? > mydb=# select * from time_recs where id_time_rec not in (select > id_time_rec from punch_time_recs); > (0 rows) > Table "public.punch_time_recs" > Column | Type | Modifiers > -------------------+------------------------+------------------------------- > id_time_rec | character varying(38) | The column in punch_time_recs is null capable. Try using NOT EXISTS. The SQL spec requires the NOT IN to be the equivalent of a "not equals" test for all entries, and you can't say that any given value is not equal to NULL, since NULL can mean that there is a value but you don't know it. The semantics of NOT EXISTS are subtly different here -- it means there aren't any rows known to have the value. -Kevin