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

# alexander(dot)farber(at)gmail(dot)com / 2006-08-18 10:00:20 +0200:
> On 8/18/06, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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