Thread: IN selection issues

I am encountering problems with IN selections.

I am trying this query :

delete from status where status_id not in (select distinct status_id
from company);

"status_id" is a foreign key in "company".

It does not delete any records from status table whereas it should
because the sub query returns some records.

Any explanation ?

Thanks

--
M. Jérôme CALAIS (Ingénieur Conseil)
Portable: 0620648297
E-Mail: jerome.calais@arunix.fr
------------------------------
Arunix
Z.A.L St Amé
62806 Liévin Cedex
France
Tel: 0321142843
Fax: 0321142831
URL: http://www.arunix.fr

Jérôme Calais writes:

> delete from status where status_id not in (select distinct status_id
> from company);
>
> "status_id" is a foreign key in "company".
>
> It does not delete any records from status table whereas it should
> because the sub query returns some records.

There are probably null values in the result set of the subselect.

--
Peter Eisentraut   peter_e@gmx.net

Jérôme Calais <jerome.calais@arunix.fr> writes:
> I am encountering problems with IN selections.
> I am trying this query :
> delete from status where status_id not in (select distinct status_id
> from company);

I'll bet the sub-select yields some NULL rows?  If so, this is not a
bug.  NOT IN is not very intuitive about handling of NULLs.  I suggest
you consult a SQL textbook ...
        regards, tom lane