Обсуждение: SELECT ... WHERE ... NOT IN (SELECT ...);
opennms=# SELECT eventid FROM tbl1;eventid --------- 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083 (10 rows) opennms=# SELECT eventid FROM tbl2;eventid --------- 123989 123989 123989 16134 16134 16368 16134 92685 92685 60115 (10 rows) opennms=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); eventid --------- (0 rows) I'm confused, the result should not 0 rows, right? Pls Help. Am I missing something? TIA. _____________________________________________________________ Get yourname@dugem.com at http://www.dugem.com _____________________________________________________________ Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag
Yon Den Baguse Ngarso <yon@dugem.com> writes:
> I'm confused, the result should not 0 rows, right?
Not what I get:
regression=# create table tbl1(eventid int);
CREATE TABLE
-- load data
regression=# SELECT eventid FROM tbl1;eventid
--------- 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083
(10 rows)
regression=# create table tbl2(eventid int);
CREATE TABLE
-- load data
regression=# SELECT eventid FROM tbl2;eventid
--------- 123989 123989 123989 16134 16134 16368 16134 92685 92685 60115
(10 rows)
regression=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2);eventid
--------- 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083
(10 rows)
I think there must be something you didn't tell us...
regards, tom lane
That's way i'm confuse. I'm using postgresql-7.1.3 I did the same action like you do. Am i missing something? TIA, Yon --- Tom Lane <tgl@sss.pgh.pa.us> wrote: >Yon Den Baguse Ngarso <yon@dugem.com> writes: >> I'm confused, the result should not 0 rows, right? > >Not what I get: > >regression=# create table tbl1(eventid int); >CREATE TABLE >-- load data >regression=# SELECT eventid FROM tbl1; > eventid >--------- > 119064 > 119064 > 60116 > 16082 > 16082 > 16303 > 16082 > 92628 > 92628 > 60083 >(10 rows) > >regression=# create table tbl2(eventid int); >CREATE TABLE >-- load data >regression=# SELECT eventid FROM tbl2; > eventid >--------- > 123989 > 123989 > 123989 > 16134 > 16134 > 16368 > 16134 > 92685 > 92685 > 60115 >(10 rows) > >regression=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); > eventid >--------- > 119064 > 119064 > 60116 > 16082 > 16082 > 16303 > 16082 > 92628 > 92628 > 60083 >(10 rows) > > >I think there must be something you didn't tell us... > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org _____________________________________________________________ Get yourname@dugem.com at http://www.dugem.com _____________________________________________________________ Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag
Oops correction. 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 nulllosteventid | 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; myhost=# --check the new tbl myhost=# \d tbl1 Table "tbl1"Attribute | Type | Modifier -----------+---------+----------eventid | integer | myhost=# SELECT eventid FROM tbl1;eventid --------- 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083 (10 rows) myhost=# \d tbl2 Table "tbl2"Attribute | Type | Modifier -----------+---------+----------eventid | integer | myhost=# SELECT eventid FROM tbl2;eventid --------- 123989 123989 123989 16134 16134 16368 16134 92685 92685 60115 (10 rows) myhost=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2);eventid --------- (0 rows) TIA, Yon --- Yon Den Baguse Ngarso <yon@dugem.com> wrote: >That's way i'm confuse. >I'm using postgresql-7.1.3 > >I did the same action like you do. >Am i missing something? > >TIA, >Yon > >--- Tom Lane <tgl@sss.pgh.pa.us> wrote: >>Yon Den Baguse Ngarso <yon@dugem.com> writes: >>> I'm confused, the result should not 0 rows, right? >> >>Not what I get: >> >>regression=# create table tbl1(eventid int); >>CREATE TABLE >>-- load data >>regression=# SELECT eventid FROM tbl1; >> eventid >>--------- >> 119064 >> 119064 >> 60116 >> 16082 >> 16082 >> 16303 >> 16082 >> 92628 >> 92628 >> 60083 >>(10 rows) >> >>regression=# create table tbl2(eventid int); >>CREATE TABLE >>-- load data >>regression=# SELECT eventid FROM tbl2; >> eventid >>--------- >> 123989 >> 123989 >> 123989 >> 16134 >> 16134 >> 16368 >> 16134 >> 92685 >> 92685 >> 60115 >>(10 rows) >> >>regression=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); >> eventid >>--------- >> 119064 >> 119064 >> 60116 >> 16082 >> 16082 >> 16303 >> 16082 >> 92628 >> 92628 >> 60083 >>(10 rows) >> >> >>I think there must be something you didn't tell us... >> >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org > >_____________________________________________________________ >Get yourname@dugem.com at http://www.dugem.com > >_____________________________________________________________ >Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly _____________________________________________________________ Get yourname@dugem.com at http://www.dugem.com _____________________________________________________________ Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag
On Friday 23 Aug 2002 9:28 am, Yon Den Baguse Ngarso wrote: > Oops correction. > > 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=# --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; These aren't creating temporary tables, they are creating permanent tables. Do you have data from previous runs in there? Try SELECT ... INTO TEMPORARY tbl1 ... - Richard Huxton
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.
regards, tom lane
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
I use a 7.3devel recently build (on a FreeBSD 4.3 box) from CVS sources. I follow all your steps, and psql results : test_db=> \! cat problem.sql --create new temp tbl1 SELECT losteventid AS eventid INTO tbl1 FROM outages; --create new temp tbl2 SELECT regainedeventid AS eventid INTO tbl2 FROM outages; SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); test_db=> \i problem.sql SELECT SELECTeventid --------- 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083 (10 rows) "Yon Den Baguse Ngarso" <yon@dugem.com> a �crit dans le message de news: 20020823082835.959193953@sitemail.everyone.net... > Oops correction. > ... cut > > myhost=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); > eventid > --------- > (0 rows) > > TIA, > Yon >
O, Yes... The problem occure because of null from selecting into tbl2. I have to delete record which eventid=null, and the result become correct. Pls be carefull with Null entri when using NOT IN. Thanks for you all :-) Regards, -Yon- --- Tom Lane <tgl@sss.pgh.pa.us> 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. > > regards, tom lane _____________________________________________________________ Get yourname@dugem.com at http://www.dugem.com _____________________________________________________________ Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag