Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Yon Den Baguse Ngarso
Date:
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

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

From
Tom Lane
Date:
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

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

From
Yon Den Baguse Ngarso
Date:
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

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

From
Yon Den Baguse Ngarso
Date:
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

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

From
Richard Huxton
Date:
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

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

From
Tom Lane
Date:
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

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

From
"Ross J. Reedstrom"
Date:
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

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

From
"OU"
Date:
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
>

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

From
Yon Den Baguse Ngarso
Date:
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