Why does IN work but NOT IN fails?
KONE (SE 7.31, Solaris 2.6) found that a NOT IN subquery between universe and holdings had stopped working, though the IN forms worked. Yogesh and Jonathan Leffler suggested NULLs in the subquery column (add 'is not null') and said to qualify columns with aliases. Steven Mastandrea noted universe was never aliased as u. No confirmed outcome.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have two tables, universe and holdings. Holdings are always drawn
from universe. There are 1650 rows in universe and 157 in holdings.
Sometimes I want to connect the tables and sometimes I want to look in
universe for items not in holdings. This NOT IN approach used to work
but has recently stopped working. (We run SE 7.31 on a Solaris 2.6
system.)
The following statements work fine:
Select u.ticker
from universe u, holdings h
where u.ticker = h.ticker;
Select u.ticker from universe
where u.ticker in ( select ticker from holdings );
but this statement fails:
Select u.ticker from universe
where u.ticker NOT in ( select ticker from holdings );
What would explain this behavior?
Thanks.
> but this statement fails:
>
> Select u.ticker from universe
> where u.ticker NOT in ( select ticker from holdings );
What do you mean by 'fail'?
---
Bashar Chalabi
CTL, London
Try adding :
where ticker is not null, in the subquery.
HTH
Yogesh
In article <386A9378.92CCA4C0@cornercap.com>,
KONE <kolney@cornercap.com> wrote:
> I have two tables, universe and holdings. Holdings are always drawn
> from universe. There are 1650 rows in universe and 157 in holdings.
> Sometimes I want to connect the tables and sometimes I want to look in
> universe for items not in holdings. This NOT IN approach used to work
> but has recently stopped working. (We run SE 7.31 on a Solaris 2.6
> system.)
>
> The following statements work fine:
> Select u.ticker
> from universe u, holdings h
> where u.ticker = h.ticker;>
> Select u.ticker from universe
> where u.ticker in ( select ticker from holdings );>
> but this statement fails:
>
> Select u.ticker from universe
> where u.ticker NOT in ( select ticker from holdings );>
> What would explain this behavior?
>
> Thanks.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Someone else mentioned nulls in the result set of the sub-query; that is a
plausible
reason for what used to work ceasing to work.
KONE wrote:
> I have two tables, universe and holdings. Holdings are always drawn
> from universe. There are 1650 rows in universe and 157 in holdings.
> Sometimes I want to connect the tables and sometimes I want to look in
> universe for items not in holdings. This NOT IN approach used to work
> but has recently stopped working. (We run SE 7.31 on a Solaris 2.6
> system.)
>
> The following statements work fine:
> Select u.ticker
> from universe u, holdings h
> where u.ticker = h.ticker;>
> Select u.ticker from universe
> where u.ticker in ( select ticker from holdings );
Error: table u not referenced in the FROM clause.
I'd probably write:
SELECT u.ticker FROM universe u
WHERE u.ticker IN (SELECT h.ticker FROM holdings h);It shouldn't make any odds, but it eliminates any chance of being
misunderstood.
> but this statement fails:
>
> Select u.ticker from universe
> where u.ticker NOT in ( select ticker from holdings );
Again, the same comments apply (qualify all column names with a table
alias).
Also, if there's a null in the holdings.ticker column, it will screw your
logic up something rotten.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
The problem is you haven't aliased your universe table to u.
Try:
> Select u.ticker from universe u
> where u.ticker NOT in ( select ticker from holdings );
OR
> Select ticker from universe
> where ticker NOT in ( select ticker from holdings );
Hope that helps,
--Steven
Jonathan Leffler wrote:
> Someone else mentioned nulls in the result set of the sub-query; that is a
> plausible
> reason for what used to work ceasing to work.
>
> KONE wrote:
>
> > I have two tables, universe and holdings. Holdings are always drawn
> > from universe. There are 1650 rows in universe and 157 in holdings.
> > Sometimes I want to connect the tables and sometimes I want to look in
> > universe for items not in holdings. This NOT IN approach used to work
> > but has recently stopped working. (We run SE 7.31 on a Solaris 2.6
> > system.)
> >
> > The following statements work fine:
> > Select u.ticker
> > from universe u, holdings h
> > where u.ticker = h.ticker;> >
> > Select u.ticker from universe
> > where u.ticker in ( select ticker from holdings );>
> Error: table u not referenced in the FROM clause.
>
> I'd probably write:
> SELECT u.ticker FROM universe u
> WHERE u.ticker IN (SELECT h.ticker FROM holdings h);> It shouldn't make any odds, but it eliminates any chance of being
> misunderstood.
>
> > but this statement fails:
> >
> > Select u.ticker from universe
> > where u.ticker NOT in ( select ticker from holdings );>
> Again, the same comments apply (qualify all column names with a table
> alias).
> Also, if there's a null in the holdings.ticker column, it will screw your
> logic up something rotten.
>
> --
> Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
> Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN
> #include <disclaimer.h>
--
-----------------------------------------------------
Steven Mastandrea stevem@cstech.com
Systems Designer 847.397.7300
CSTech, Inc. Schaumburg, IL