Re: select * from users where user_id NOT in (select - Mailing list pgsql-general

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