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]

 



# alexander.farber@xxxxxxxxx / 2006-08-18 10:00:20 +0200:
> On 8/18/06, Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> 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


[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