Thread: select * from users where user_id NOT in (select ban_userid from banlist)
select * from users where user_id NOT in (select ban_userid from banlist)
From
"Alexander Farber"
Date:
Hello,
I have this strange problem that the following statement works:
phpbb=> select user_id, username from phpbb_users
phpbb-> where user_id in (select ban_userid from phpbb_banlist);
user_id | username
---------+----------
3 | La-Li
(1 row)
But the negative one returns nothing:
phpbb=> select user_id, username from phpbb_users
phpbb-> where user_id not in (select ban_userid from phpbb_banlist);
user_id | username
---------+----------
(0 rows)
Eventhough there are 3 other users in the phpbb_users table:
phpbb=> select user_id, username from phpbb_users;
user_id | username
---------+-----------
-1 | Anonymous
3 | La-Li
4 | Vasja
2 | Alex
(4 rows)
And there is only one user (La-Li, id=3) in the phpbb_banlist:
phpbb=> select * from phpbb_banlist;
ban_id | ban_userid | ban_ip | ban_email
--------+------------+--------+-------------
1 | 3 | |
4 | | | *@gmail.com
(2 rows)
Thank you
Alex
On Thu, 17 Aug 2006, Alexander Farber wrote: > I have this strange problem that the following statement works: NULLs are not your friends. :( > phpbb=> select user_id, username from phpbb_users > phpbb-> where user_id in (select ban_userid from phpbb_banlist); > user_id | username > ---------+---------- > 3 | La-Li > (1 row) > > > But the negative one returns nothing: > > phpbb=> select user_id, username from phpbb_users > phpbb-> where user_id not in (select ban_userid from phpbb_banlist); > user_id | username > ---------+---------- > (0 rows) Sadly, these two look like they would give you all the users rows, but they don't because of the NULL ban_userid. When the subselect returns NULL for at least one row, you fall into this sort of case. x NOT IN (...) is equivalent to NOT(x IN (...)) which is NOT(x = ANY (...)) x = ANY (...) is basically defined as True if x = y is true for some y in the subselect False if x = y is false for all y in the subselect Unknown otherwise Since x = NULL is unknown and not true or false, you fall into the last case with your query and data. > Eventhough there are 3 other users in the phpbb_users table: > > phpbb=> select user_id, username from phpbb_users; > user_id | username > ---------+----------- > -1 | Anonymous > 3 | La-Li > 4 | Vasja > 2 | Alex > (4 rows) > > And there is only one user (La-Li, id=3) in the phpbb_banlist: > > phpbb=> select * from phpbb_banlist; > ban_id | ban_userid | ban_ip | ban_email > --------+------------+--------+------------- > 1 | 3 | | > 4 | | | *@gmail.com > (2 rows)
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 17 Aug 2006, Alexander Farber wrote:
>> But the negative one returns nothing:
>>
>> phpbb=> select user_id, username from phpbb_users
>> phpbb-> where user_id not in (select ban_userid from phpbb_banlist);
>> user_id | username
>> ---------+----------
>> (0 rows)
> Sadly, these two look like they would give you all the users rows, but
> they don't because of the NULL ban_userid. When the subselect returns
> NULL for at least one row, you fall into this sort of case.
Seems like the NULLs-in-NOT-IN thing ought to be in our FAQ list.
It certainly bites newbies often enough :-(
regards, tom lane
Hello, thank you and sorry for asking a FAQ. I've fixed my problem now by: select user_id, username from phpbb_users where user_id not in (select ban_userid from phpbb_banlist where ban_userid is not null); but still your explanation feels illogical to me even though I know you're right... On 8/18/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > When the subselect returns > NULL for at least one row, you fall into this sort of case. > > x NOT IN (...) is equivalent to NOT(x IN (...)) which is > NOT(x = ANY (...)) > > x = ANY (...) is basically defined as > True if x = y is true for some y in the subselect > False if x = y is false for all y in the subselect > Unknown otherwise > > Since x = NULL is unknown and not true or false, you fall into the last > case with your query and data. > Regards Alex -- http://preferans.de
# alexander.farber@gmail.com / 2006-08-18 10:00:20 +0200:
> On 8/18/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >When the subselect returns
> >NULL for at least one row, you fall into this sort of case.
> >
> >x NOT IN (...) is equivalent to NOT(x IN (...)) which is
> >NOT(x = ANY (...))
> >
> >x = ANY (...) is basically defined as
> > True if x = y is true for some y in the subselect
> > False if x = y is false for all y in the subselect
> > Unknown otherwise
> >
> >Since x = NULL is unknown and not true or false, you fall into the last
> >case with your query and data.
>
> I've fixed my problem now by:
>
> select user_id, username from phpbb_users where user_id not in
> (select ban_userid from phpbb_banlist where ban_userid is not null);
>
> but still your explanation feels illogical
> to me even though I know you're right...
The confusion comes from mismatch between the meaning of NULL
in languages like C where it means NONE, and SQL, where it's more
like ANY/UNKNOWN. I believe it'll make sense once you buy the latter
meaning.
Since NULL means UNKNOWN, can you tell which ids from (1, 2, 3, 4)
are ABSOLUTELY NOT in (1, UNKNOWN)? You can't, because you don't
know what that UNKNOWN (IOW NULL) is.
It is unknown whether an unknown value equals any other value:
test=# select coalesce((1 = NULL)::int::text, 'UNKNOWN');
coalesce
----------
UNKNOWN
(1 row)
test=# select coalesce((NULL = NULL)::int::text, 'UNKNOWN');
coalesce
----------
UNKNOWN
(1 row)
Thus, given these data
test=# create table a (id int);
test=# create table b (id int);
test=# insert into a values (1);
test=# insert into a values (2);
test=# insert into a values (3);
test=# insert into a values (4);
test=# insert into b values (1);
test=# insert into b values (NULL);
this query
test=# select * from a where id not in (select * from b);
must return an empty set, because the NULL in b might
stand for any of the four values in a.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991