Re: Understanding INNER JOIN versus IN subquery

On Mon, Jul 20, 2009 at 2:37 PM, Robert James<srobertjames@gmail.com> wrote:
> I have two queries which should be equivalent.  The Planner plans them
> differently, although they are both about the same time.  Can someone
> explain why?
> select word from dict
> where
>  word in
>  (select substr('moon', 0, generate_series(3,length('moon'))))
>
> select * from dict
> inner join (select substr('moon', 0, generate_series(3,length('moon')))) as
> m
> on dict.word = m.substr
>
> Is one preferred?

it is hard to say from your example. But in my general expierence, I
noticed similar thing many times.
Needless to say, I prefer personally to choose JOINs instead of IN(),
because those tend to be faster on postgresql.