sistemas 95572 (1/29/2010)
Assuming that:* the inner select returns a reasonable amount of rows - so it fits in memory
shouldn't the IN statement be better since the inner query executes only once, storing in memory all those returned int values (using some kind of ordered array), and then perform a bin search for each value returned by the outer select?
Well, that assumes that the inner query would only execute once. I'm not sure we can guarantee that. In fact, I'm such a pessimist that I would expect the worst case, that the inner query would execute once for each row returned by the outer query. Now, I'm quite sure that the optimizer would handle a good bit of this overhead and give me a pleasant surprise, but maybe not.
You may be right in this case. I simply can't tell.