Skip site navigation (1) Skip section navigation (2)

Question regarding 'not in' and subselects

From: Vic Ricker <vicricker(at)charter(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Question regarding 'not in' and subselects
Date: 2004-05-26 17:57:37
Message-ID: 1085594256.3529.39.camel@tablet.ricker.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi.  I'm using PostgreSQL 7.4.2 under Fedora Core 1.

I have two tables with a single varchar(32) column in each.  I'm trying
to find all the rows from one table that don't exist in the other
table.  The query that I am using is:

select u.user_name from users u where u.user_name not in (select
user_name from iasusers);

(Actually, I'm doing something a bit more complex but this illustrates
the problem.)

It always seems to return 0 rows.  As a test, I inserted a row into
users that I knew wasn't in iasuses but it didn't make a difference.

If I remove the 'not', the query returns the rows that exist in both
tables.

If I replace the subselect with a list, it seems to work the way that
I'd expect, i.e. "not in ('vic', 'joe')" it shows all the rows from
users except for vic and joe.

Am I doing something wrong or is this a bug?

Thanks,
-Vic





Responses

pgsql-bugs by date

Next:From: Bruno Wolff IIIDate: 2004-05-26 18:38:24
Subject: Re: Question regarding 'not in' and subselects
Previous:From: Richard HuxtonDate: 2004-05-26 16:18:39
Subject: Re: BUG #1156: Database is not initializing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group