ODP: Problem in dbaccess (Informix 7.2)

Exactly this answer did cost me 2 days of work.:-)) I still don't know how it works, but thanks for your help. Tomek Frydryk Bank Staropolski. -----Oryginalna wiadomo=B6=E6----- Od: Mark Collins [SMTP:mcollins@us.DHL.COM] Wys=B3ano: 15 pa=BCdziernika 1997 16:25 Do: informix-list@rmy.emory.edu; Tomek Frydryk Temat: Re: Problem in dbaccess (Informix 7.2)=20 > Little problem. > We have 2 tables A and B. In table A we have column numer (SERIAL = type). In > B we have column A_id. which is a key to A.numer (is this clear?). In = table > A we have 80000 records, in B 20000 records. > And now we're making 3 selects: > 1) select count(*) from A; > Answer: 80000. > 2)select count(*) from A where numer in (select A_id from B); > Answer: 20000. > 3)select count(*) from A where numer not in (select A_id from B); > Answer: 0 > Question: the 3rd select must return 60000, or not? Why there is zero? = What > is the tip? Try "select count(*) from B where A_id is null". If you have any nulls, = query 3 will=20 not work as it is written. Modify it to be "select count(*) from A = where numer not in=20 (select A_id from B where A_id is not null)". That should get 60000. =20 =20 =20 Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.