Subject RE: [firebird-support] Re: IN clause - using static values vs table values
Author Rick Debay

The IN SELECT will be executed once for every row in X.

-----Original Message-----
[] On Behalf Of Gary Benade
Sent: Tuesday, August 08, 2006 8:39 AM
Subject: Re: [firebird-support] Re: IN clause - using static values vs
table values

> Adam wrote,
> A couple of hundred is OK, although it hits the index for EACH member.
> You will have problems if you try with 1500 though.
> It does not make sense to delete from a join. You can do delete from
> sometable where ID in (select ID from sometable join ....)

Adam, sorry if I keep hammering on the same point, but say I had 1500
items in my list. Would it be more efficient to use a hard coded IN
clause where the index gets hit once per item, or would it be better to
use the DELETE FROM X WHERE Y IN( SELECT ....) method.
I would assume that firebird would have to do more work in the second
case by having to generate the list that I would have supplied as part
of the query, and in the process also generating index hits. I guess
there is additional work involved in parsing the IN clause in the case
of DELETE FROM X WHERE Y IN(1,2,3), but is it more work than in the
first case?

Thanks for your time, much appreciated


Visit and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at


Yahoo! Groups Links