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