> hi, im so interested about IN clause too,
> id like to ask also why:
>
> A) .. where afield in (select bfield from [some millions row])
> B) .. where afield in (select distinct bfield .... (some houndreds
> row in
> resultset, same table of A)
> C) .. where afield in (select <distinct> bfield ....
> A and B/C differs only by primary key/index on bField, aField is always
> indexed, not primary key
> and really looks (at my eyes) so slow.
>
> i really dont know how to improve it, dont tell me to redesign DB as fb
> support did with others before, i know (or i think to know) how to
> design
> DB, and i ckecked it so much before posting here: its ok.. and MS-
> SQL2005,
> with same tables, same data.. is really faster on that query. (ONLY on
> that...)
>
> fb is really great (or better than other) for a lot of things.. but,
> for my
> point of view, the IN clause is not so good on fb 2+ (i never used
> previous
> verions)
> thanks, marco

you'll find euqal performance with other DBs if you use EXISTS clause
instead of WHERE ..IN
Alan