Question regarding 'not in' and subselects

Lists: Postg배트맨 토토SQL : Postg배트맨 토토SQL 메일 링리스트 : 2004-05-26 이후 PGSQL-BUGS 17:57
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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg배트맨 토토SQL : Postg배트맨 토토SQL 메일 링리스트 : 2004-05-26 이후 PGSQL-BUGS 17:57

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

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Vic Ricker <vicricker(at)charter(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Question regarding 'not in' and subselects
Date: 2004-05-26 18:38:24
Message-ID: 20040526183824.GA3739@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg메이저 토토 사이트SQL : Postg메이저 토토 사이트SQL 메일 링리스트 : 2004-05-26 이후 PGSQL-BUGS 18:38

On Wed, May 26, 2004 at 13:57:37 -0400,
Vic Ricker <vicricker(at)charter(dot)net> wrote:
> 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?

Are there any nulls in iasusers.user_name?

From: Vic Ricker <vicricker(at)charter(dot)net>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Question regarding 'not in' and subselects
Date: 2004-05-26 19:42:06
Message-ID: 1085600526.3926.7.camel@tablet.ricker.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg와이즈 토토SQL : Postg와이즈 토토SQL 메일 링리스트 : 2004-05-26 이후 PGSQL-BUGS 19:42

On Wed, 2004-05-26 at 14:38, Bruno Wolff III wrote:
> On Wed, May 26, 2004 at 13:57:37 -0400,
> Vic Ricker <vicricker(at)charter(dot)net> wrote:
> > Am I doing something wrong or is this a bug?
>
> Are there any nulls in iasusers.user_name?

There was a null in iasusers. I removed it and that fixed the problem.
I'm not sure that I understand why. It doesn't seem very intuitive...
:-)

Tom Innes's suggestion of:

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

also worked.

Thanks guys!

--
Vic Ricker
http://www.ricker.us/