[GENERAL] select * from users where user_id NOT in (select ban_userid from banlist)

[Alexander Farber]

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 |            |        | [EMAIL PROTECTED]
(2 rows)

Thank you
Alex

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match