Thread: I don't understand...

Hi All ,

SELECT DISTINCT h_name FROM haszon;
+---------------+
| h_name        |
+---------------+
| CITROEN       |
| DAEWOO-FSO    |
| DAEWOO-LUBLIN |
| FIAT          |
| FORD          |
| GAZ           |
| HYUNDAI       |
| KIA           |
| MAZDA         |
| MERCEDES BENZ |
| MITSUBISHI    |
| NISSAN        |
| OPEL          |
| PEUGEOT       |
| RENAULT       |
| SEAT          |
| SKODA         |
| SUZUKI        |
| TATA          |
| TOYOTA        |
| VOLKSWAGEN    |
+---------------+
Query OK, 21 rows in set (0,20 sec)

SELECT cn_name FROM carname

+---------------+
| cn_name       |
+---------------+
| ALFA ROMEO    |
| AUDI          |
| BMW           |
| CHRYSLER      |
| CITROEN       |
| DAEWOO        |
| DAIHATSU      |
| DAIMLER       |
| FIAT          |
| FORD          |
| HONDA         |
| HYUNDAI       |
| JAGUAR        |
| JEEP          |
| KIA           |
| LADA          |
| LANCIA        |
| LAND ROVER    |
| LEXUS         |
| MAZDA         |
| MERCEDES BENZ |
| MG            |
| MITSUBISHI    |
| NISSAN        |
| OPEL          |
| PEUGEOT       |
| PORSCHE       |
| PROTON        |
| RENAULT       |
| ROVER         |
| SAAB          |
| SEAT          |
| SKODA         |
| SUBARU        |
| SUZUKI        |
| TOYOTA        |
| VOLKSWAGEN    |
| VOLVO         |
| <Null>        |
+---------------+
Query OK, 39 rows in set (0,35 sec)

SELECT DISTINCT h_name
FROM haszon
WHERE h_name IN (SELECT cn_name FROM carname)

+---------------+
| h_name        |
+---------------+
| CITROEN       |
| FIAT          |
| FORD          |
| HYUNDAI       |
| KIA           |
| MAZDA         |
| MERCEDES BENZ |
| MITSUBISHI    |
| NISSAN        |
| OPEL          |
| PEUGEOT       |
| RENAULT       |
| SEAT          |
| SKODA         |
| SUZUKI        |
| TOYOTA        |
| VOLKSWAGEN    |
+---------------+
Query OK, 17 rows in set (0,22 sec)

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

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

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

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