Date: Fri, 23 Aug 2002 10:01:34 -0500
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Yon Den Baguse Ngarso <yon@dugem.com>,
	Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT ... WHERE ... NOT IN (SELECT ...);
Message-ID: <20020823150133.GB32649@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Yon Den Baguse Ngarso <yon@dugem.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-sql@postgresql.org
References: <20020823082835.959193953@sitemail.everyone.net>
	<19392.1030109144@sss.pgh.pa.us>
	<20020823082835.959193953@sitemail.everyone.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <19392.1030109144@sss.pgh.pa.us>
	<20020823082835.959193953@sitemail.everyone.net>
User-Agent: Mutt/1.3.27i

On Fri, Aug 23, 2002 at 09:25:44AM -0400, Tom Lane wrote:
> Yon Den Baguse Ngarso <yon@dugem.com> writes:
> > If i create tbl1 & tbl2, and then load it with the data.
> > The result is CORRECT. Like yours.
> > But, if the data loaded from another table, the result become WRONG/ null record. 
> 
> Do you have any nulls in what you are selecting into tbl2?
> The behavior of NOT IN with nulls is not very intuitive.
> 

I think Tom has hit the nail on the head - Based on the schema of the
table that's being selected from, I'd guess there's a heap of NULLs
in losteventid.  Yon, the behavior of IN and NOT IN with NULL can be
described if you think of NULL as DONTKNOW or MAYBE. Is 'a' in the set
('b','c','d')? No. Is it in the set ('b','c',NULL)? MAYBE. is it NOT in
the set? MAYBE.

Ross "will explain tri-valued logic for beer" Reedstrom


On Fri, Aug 23, 2002 at 01:28:35AM -0700, Yon Den Baguse Ngarso wrote:
> 
> If i create tbl1 & tbl2, and then load it with the data.
> The result is CORRECT. Like yours.
> 
> But, if the data loaded from another table, the result become WRONG/ null record. 
> 
> Here is my detail step. Please Help.
> 
> ----
> 
> myhost=# \d outages
>                      Table "outages"
>      Attribute      |           Type           | Modifier
> --------------------+--------------------------+----------
>  outageid           | integer                  | not null
>  losteventid        | integer                  |
>  regainedeventid    | integer                  |
> 
> myhost=# --create new temp tbl1
> myhost=# SELECT losteventid AS eventid INTO tbl1 FROM outages;
> myhost=# --create new temp tbl2
> myhost=# SELECT regainedeventid AS eventid INTO tbl2 FROM outages;
> 

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org