Re: SELECT ... WHERE ... NOT IN (SELECT ...);

On Fri, Aug 23, 2002 at 09:25:44AM -0400, Tom Lane wrote:
> Yon Den Baguse Ngarso <yon(at)dugem(dot)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