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.

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@gmx.net http://funkturm.homeip.net/~peter

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