I have a table orders and a table orderpositions. The orderpositions (basically the items bought with the order) are linked via orderid (indexed) to the orders primary key.
For a given set of orders I need the orderpositions. Because the conditions with which the orders are determined are dynamic I need to use a subquery instead of a JOIN, so I wrote:
SELECT * FROM orderpositions WHERE orderpositions.orderid IN ( SELECT id FROM orders WHERE userid=118 );
(The sub-query can be something entirely different, perhaps with nested JOINS and whatnot, but will always return a list of order IDs, of which I need the linked orderpositions.)
Now this runs significantly slower than the JOIN equivalent (which is trivial in this example, but not in others). I have read about correlated queries and believe that my approach is very slow because the subquery is executed once for each row in the orderpositions table.
As a test I have tried to create a temporary table, insert the order IDs into it and then JOIN on that, which is also very fast. But I would like to avoid the overhead of the temporary table and firing off multiple queries.
Is there any way to force the sub-query to be uncorrelated?