Re: I don't understand...

Gabor Csuri writes:

> 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 ?

Because one of the cn_name values is NULL. Observe the semantics of the
IN operator if the set contains a NULL value:

h_name NOT IN (a, b, c)
NOT (h_name = a OR h_name = b OR h_name = c)

Say c is null:

NOT (h_name = a OR h_name = b OR h_name = NULL)
NOT (h_name = a OR h_name = b OR NULL)
NOT (NULL)
NULL

which is false.

You might want to add a ... WHERE cn_name IS NOT NULL in the subquery.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter