Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 960962E002C for ; Tue, 1 Apr 2008 13:04:39 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90420-01 for ; Tue, 1 Apr 2008 13:04:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by developer.postgresql.org (Postfix) with ESMTP id AAFFD2E0066 for ; Tue, 1 Apr 2008 13:04:33 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 2AC6A3637B; Tue, 1 Apr 2008 09:04:22 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 294FA3636E; Tue, 1 Apr 2008 09:04:22 -0700 (PDT) Date: Tue, 1 Apr 2008 09:04:22 -0700 (PDT) From: Stephan Szabo To: Raphael Bauduin Cc: pgsql-sql@postgresql.org Subject: Re: difference between EXCEPT and NOT IN? In-Reply-To: Message-ID: <20080401085733.F16796@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/3 X-Sequence-Number: 30542 On Tue, 1 Apr 2008, Raphael Bauduin wrote: > The 2 following statements don't give the same result. I expected the > second ti give the exact same result as the first one. If any entree_id can be NULL they aren't defined to give the same result. EXCEPT is defined in terms of duplicates based on distinctness, and for example (1 is distinct from 1) is false, (1 is distinct from NULL) is true and (NULL is distinct from NULL) if false. NOT IN is defined in terms of equality, and for example, (1=1) is true, (1=NULL) is unknown and (NULL=NULL) is unknown.