Re: 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).