On 1/22/07, Harald Armin Massa <[EMAIL PROTECTED]> wrote:
>> select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...) > > I usually try to rewrite this kind of queries to select whatever from table t1 join (select table_id from xxxxx where xxxxx) t2 using (table_id) And 3 out of 4 this performs better on Oracle and PostgreSQL. Would be curious why it does , but usually I am happy that it does:)
Because the results would be different than a subselect, less work = faster. One thing to point out is that a query of the form: select ... from foo where id in (select id from bar where n=27) Would normally result in a SORT UNIQUE for the "select id from bar where n=27" part. Where as: select ... from foo f1, (select id from bar where n=27) f2 where f1.id = f2.id is the same as... select ... from foo f1, bar f2 where f2.n=27 and f1.id=f2.id which would not result in a sort unique. In order to obtain the same results as a subselect you would need to group or distinct, and I would imagine the results would be the same as the IN..SUBSELECT -- Chad http://www.postgresqlforums.com/