Re: Re: I don't understand...

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