Why does IN work but NOT IN fails?
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