> > > It appears to me that to follow the SQL spec, a NULL found in a.i
> > > should return NULL for both IN and NOT IN (the spec appears to say that
> > > the result of IN is "unknown" in that case, and we are using NULL to
> > > represent "unknown"):
> >
> > I would be interested to see how other databases handle this.
> >
>
> ----------------------------------------------
> create table a (i int, aa char(10));
> create table b (i int, bb char(10));
> insert into a values(1, 'foo');
> insert into b values(null, 'bar');
> select * from a where i not in (select i from b);
> -----------------------------------------------
> I tried the above script on:
>
> Informix-SE
> Oracle8
>
> and both of them return 0 rows, like PostgreSQL.
>
Yes, this is how I remembered Informix doing it. Returning a NULL in
the subselect does not match anything, so hopefully we don't have a bug.
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026