Re: [GENERAL] select * from users where user_id NOT in (select
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] select * from users where user_id NOT in (select
# [EMAIL PROTECTED] / 2006-08-18 10:00:20 +0200: On 8/18/06, Stephan Szabo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] select * from users where user_id NOT in (select
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 ||| [EMAIL PROTECTED] (2 rows) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] select * from users where user_id NOT in (select
Stephan Szabo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
