Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"NOT IN" not working 1

Status
Not open for further replies.

sojankurian

Programmer
Sep 30, 2002
16
US
Informix IDS 7.31,

Query like this one is not working on my server

select count(*) from t2
where fld1 not in (select fld1 from t1 )

But I have used this kind of SQL earlier on same version of informix.

I asked the same question to DBA, as per him "This behavior is the same across the engine versions" his suggestion is as given below,

select count(*) from t2
where t2.fld1 not in (select t1.fld1 from t1,t2 where t1.fld1=t2.fld1);

Any idea informix doesn't support "not in" if the second query return large volume of rows. Can it be solved by changing some parameters?

Thank you very much.
 
we have ids 9.40 and your (simple) syntax works fine in our env, even with millions of rows in t2.

I agree to your dba that the second syntax is the better way, because it makes use of an index, if there is one and omits a sequential scan.
 
Feel free to shoot this down,but......

Select count(t2.fld1) from t2 inner join t1 on t1.fld1 <>
t2.fld2

I would think this would give the count of all the records in t2 that have no matching record in t1.

 
true,

you also could write that as:

Code:
select count(t2.fld1) from t2, t1
where t2.fld1 <> t1.fld1

This is called a theta-join, i.e. a join via not-equal and results in a query plan, which scans the outer table (t2) and looks into the inner table for each record, if there is a match.
 
I have seen that syntax as well.

And thank you for supplying the term. I neve knew what it was called before.

Perhaps now I can impress my Boss :)

 
hehe, good luck and thanks for the star.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top