Which is better? A join or a SELECT IN? I have a set of ids that I extract using a join of 2 tables.
Now I need some information from 2 other tables. What would normally be the best approach?
So a SELECT IN using the set of ids I already have, against each of the other 2 tables OR
Should I try to join these tables to the first 2 I originally do to get the set of IDs?
Is there a standard practice?
Without more information about the size of data, what relevant indexes exist and the number of likely matched that will be in the "IN" list, "it depends" is the best answer you will get. Also I'm assuming you are meaning SELECT &lt;something&gt; FROM &lt;somewhere&gt; WHERE &lt;column&gt; IN &lt;set&gt; - if not then my answer will be less relevant. Giving sample queries in your questions will help improve the answers you get in response.

As a general rule though an IN clause with static values can not be parametrised so you will end up using ad-hoc SQL which is generally best avoided for a number of reasons.

If you are using SELECT within the IN clause and you are checking against a column in the inner select that is a primary key or otherwise declared UNIQUE then you will often find that the query planner will end up doing the same thing as the simple join (certainly this is often the case with MSSQL) so at that point it is a question of style/preference. I don't know if mySQL would see this equivalence or not: to test this use the EXPLAIN method to see what it decides is possible+appropriate.

Do remember though that the results of SELECT stuff FROM here WHERE here.property IN (SELECT match FROM there) could be quite different from SELECT stuff FROM here JOIN there ON there.match = here.property - the above comments are only relevant to circumstances where the two are going to be equivalent. The former will return at most one row for each found in here even if there are several rows in there where there.match = here.property where in the latter case one copy of the row from here will be returned for each match found in there. Where there is a difference in possible output the decision is made for you: use the one that gives the right result! As another general rule (for which there are exceptions, as always with general rules!) do try avoid using the JOIN method with a DISTINCT modifier where the IN variant would work.