Re: Problem in dbaccess (Informix 7.2)
On 15 Oct 97 09:57:26 GMT, "Tomek Frydryk" <tomekf@sylaba.poznan.pl>
wrote:
>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?
Use the exists/not exists ie
select count(*) from A where exists (select * from B whereB.a_id=A.a_id);
select count(*) from A where not exists (select * from B whereB.a_id=A.a_id);
This way Informix can also make better use of your indexes and avoid
some temp table building.
Regards,
Jason
Jason Harris
Informix DBA
Westpac Banking Corporation
NOTE: I add all these address at the bottom of people who spam me
Now when their extractors extract my e-mail address they will also
end up spamming each other.
wtcjr@ix.netcom.com
Shevsky@hotmail.com
speech@speechrecognition.com
SmartBiz@NevWest.com
slender@juice4u.net
sbliss@earthfriends.com
ron@moneyaction.com
removeme@gwh.net
rocco@lostvegas.com
market@DRAWBRIDGE.NET
Foxyrusski@mail-response.com
emaster@email-man.com
waynesmail@earthlink.net