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

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.

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

Hello,

thank you and sorry for asking a FAQ.
I've fixed my problem now by:

select user_id, username from phpbb_users where user_id not in
(select ban_userid from phpbb_banlist where ban_userid is not null);

but still your explanation feels illogical
to me even though I know you're right...

On 8/18/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

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.

Regards
Alex

--
http://preferans.de

# alexander.farber@gmail.com / 2006-08-18 10:00:20 +0200:

On 8/18/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

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.

I've fixed my problem now by:

select user_id, username from phpbb_users where user_id not in
(select ban_userid from phpbb_banlist where ban_userid is not null);

but still your explanation feels illogical
to me even though I know you're right...

The confusion comes from mismatch between the meaning of NULL
in languages like C where it means NONE, and SQL, where it's more
like ANY/UNKNOWN. I believe it'll make sense once you buy the latter
meaning.

Since NULL means UNKNOWN, can you tell which ids from (1, 2, 3, 4)
are ABSOLUTELY NOT in (1, UNKNOWN)? You can't, because you don't
know what that UNKNOWN (IOW NULL) is.

It is unknown whether an unknown value equals any other value:

test=# select coalesce((1 = NULL)::int::text, 'UNKNOWN');
coalesce
----------
UNKNOWN
(1 row)

test=# select coalesce((NULL = NULL)::int::text, 'UNKNOWN');
coalesce
----------
UNKNOWN
(1 row)

Thus, given these data

test=# create table a (id int);
test=# create table b (id int);

test=# insert into a values (1);
test=# insert into a values (2);
test=# insert into a values (3);
test=# insert into a values (4);

test=# insert into b values (1);
test=# insert into b values (NULL);

this query

test=# select * from a where id not in (select * from b);

must return an empty set, because the NULL in b might
stand for any of the four values in a.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991