[HACKERS] Re: I don't understand...
Hi All again, after I deleted the null row from carname: SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) +---+ | h_name| +---+ | DAEWOO-FSO| | DAEWOO-LUBLIN | | GAZ | | TATA | +---+ Query OK, 4 rows in set (0,13 sec) It's working now, but is it correct? Bye, Gabor. I think it's good, but SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) ++ | h_name | ++ ++ Query OK, 0 rows in set (0,10 sec) Why ? postgres-7.1 rpm on RedHat 7.0 Thanks, Gabor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: I don't understand...
Gabor - Tri-valued logic strikes again. Remember, NULL represents don't know, which means could be anything. So, when you ask the system to return values that are guaranteed not to be in a list, and that list contains a NULL, the system returns nothing, since the NULL _could_ be equal to the whatever value you're comparing against: the system just doesn't know. The operational fixes are: 1) delete nulls where they're not appropriate or better 2) use NOT NULL constraints everywhere you can. and 3) use WHERE NOT NULL in your subselects, if NULL is appropriate in the underlying column Ross On Mon, May 21, 2001 at 01:09:09PM +0200, Gabor Csuri wrote: Hi All again, after I deleted the null row from carname: SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) +---+ | h_name| +---+ | DAEWOO-FSO| | DAEWOO-LUBLIN | | GAZ | | TATA | +---+ Query OK, 4 rows in set (0,13 sec) It's working now, but is it correct? Bye, Gabor. I think it's good, but SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) ++ | h_name | ++ ++ Query OK, 0 rows in set (0,10 sec) Why ? postgres-7.1 rpm on RedHat 7.0 Thanks, Gabor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: 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: [HACKERS] Re: I don't understand...
On Mon, 21 May 2001, Gabor Csuri wrote: Hi All again, after I deleted the null row from carname: SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) +---+ | h_name| +---+ | DAEWOO-FSO| | DAEWOO-LUBLIN | | GAZ | | TATA | +---+ Query OK, 4 rows in set (0,13 sec) It's working now, but is it correct? Yep. :( SQLs NULLs give lots of pain and suffering. NULL is an unknown value, so you can know that there *IS* a matching row, but you never know with certainty that there *ISN'T* a matching row when a NULL is involved. Basically IN says, if row1=row2 is true for any row, return true; if row1=row2 is false for every row return false; otherwise return NULL. When it gets to the comparison with the NULL, row1=row2 gives a NULL not a false, so the IN returns NULL (which won't get through the where clause). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
