From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select * from users where user_id NOT in (select |
Date: | 2006-08-17 22:36:33 |
Message-ID: | 20060817144613.L50269@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | | | *(at)gmail(dot)com
> (2 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Chris | 2006-08-18 00:34:44 | Re: count and limit |
Previous Message | Walter Vaughan | 2006-08-17 22:01:33 | Re: Newbie "Copy From" not working |