Search Postgresql Archives

Re: select * from users where user_id NOT in (select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux