pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT ... WHERE ... NOT IN (SELECT ...);
9+ messages / 5 participants
[nested] [flat]
* SELECT ... WHERE ... NOT IN (SELECT ...);
@ 2002-08-23 03:39 Yon Den Baguse Ngarso <yon@dugem.com>
2002-08-23 05:28 ` Re: SELECT ... WHERE ... NOT IN (SELECT ...); Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Yon Den Baguse Ngarso @ 2002-08-23 03:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
2002-08-23 03:39 SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
@ 2002-08-23 05:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2002-08-23 05:28 UTC (permalink / raw)
To: yon@dugem.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
@ 2002-08-23 07:55 Yon Den Baguse Ngarso <yon@dugem.com>
0 siblings, 0 replies; 9+ messages in thread
From: Yon Den Baguse Ngarso @ 2002-08-23 07:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
@ 2002-08-23 08:28 Yon Den Baguse Ngarso <yon@dugem.com>
2002-08-23 09:24 ` Re: SELECT ... WHERE ... NOT IN (SELECT ...); Richard Huxton <dev@archonet.com>
2002-08-23 13:25 ` Re: SELECT ... WHERE ... NOT IN (SELECT ...); Tom Lane <tgl@sss.pgh.pa.us>
2002-08-23 15:01 ` Re: SELECT ... WHERE ... NOT IN (SELECT ...); Ross J. Reedstrom <reedstrm@rice.edu>
2002-08-24 19:23 ` Re: SELECT ... WHERE ... NOT IN (SELECT ...); OU <moebius444@hotmail.com>
0 siblings, 4 replies; 9+ messages in thread
From: Yon Den Baguse Ngarso @ 2002-08-23 08:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 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;
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
2002-08-23 08:28 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
@ 2002-08-23 09:24 ` Richard Huxton <dev@archonet.com>
3 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2002-08-23 09:24 UTC (permalink / raw)
To: yon@dugem.com, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
2002-08-23 08:28 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
@ 2002-08-23 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2002-08-23 13:25 UTC (permalink / raw)
To: yon@dugem.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
2002-08-23 08:28 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
@ 2002-08-23 15:01 ` Ross J. Reedstrom <reedstrm@rice.edu>
3 siblings, 0 replies; 9+ messages in thread
From: Ross J. Reedstrom @ 2002-08-23 15:01 UTC (permalink / raw)
To: Yon Den Baguse Ngarso <yon@dugem.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
2002-08-23 08:28 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
@ 2002-08-24 19:23 ` OU <moebius444@hotmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: OU @ 2002-08-24 19:23 UTC (permalink / raw)
To: pgsql-sql
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
SELECT
eventid
---------
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT ... WHERE ... NOT IN (SELECT ...);
@ 2002-08-29 06:14 Yon Den Baguse Ngarso <yon@dugem.com>
0 siblings, 0 replies; 9+ messages in thread
From: Yon Den Baguse Ngarso @ 2002-08-29 06:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; yon@dugem.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-08-29 06:14 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-23 03:39 SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
2002-08-23 05:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-23 07:55 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
2002-08-23 08:28 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
2002-08-23 09:24 ` Richard Huxton <dev@archonet.com>
2002-08-23 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-23 15:01 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-08-24 19:23 ` OU <moebius444@hotmail.com>
2002-08-29 06:14 Re: SELECT ... WHERE ... NOT IN (SELECT ...); Yon Den Baguse Ngarso <yon@dugem.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox