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)
