On Fri, Nov 5, 2010 at 9:29 AM, Oliveiros d'Azevedo Cristina
<oliveiros.cristina@marktest.pt> wrote:
> Scott,
>
> I'd like to take this chance to ask another (related though) question.
>
> What is faster? A JOIN or a WHERE a IN (SELECT ... )
>
> I've heard that the nested subquery has a tendency to be slower, but I'd
> like to check it with people from the list.
> Is one faster than the other? Or, all in all, they run at the same speed ?
Usually the join is faster. But there's history to consider. Back in
the olden days, it was almost always faster to use a join than a where
in. Then where in got a lot of work, and it's a lot faster. So, it
depends a bit on which version your running and size of the data set
in the in. Most of the time it's pretty close, but for larger numbers
of rows in the subselect, the join is usually faster.
Of course the only way to know for sure is to test your query and see.
Make sure to run it multiple times to overcome differences in caching.
