INNER JOIN vs INNER JOIN (SELECT . FROM)

You are correct. You did exactly the right thing, checking the query plan rather than trying to second-guess the optimiser. :-)

There won't be much difference. Howver version 2 is easier when you have some calculations, aggregations, etc that should be joined outside of it

--Version 2 SELECT p.Name, s.OrderQty FROM Product p INNER JOIN (SELECT ProductID, SUM(OrderQty) as OrderQty FROM SalesOrderDetail GROUP BY ProductIDHAVING SUM(OrderQty) >1000) s on p.ProductID = s.ProdctId 

Seems to be identical just in case that SQL server will not try to read data which is not required for the query, the optimizer is clever enough

It can have sense when join on complex query (i.e which have joings, groupings etc itself) then, yes, it is better to specify required fields.

But there is one more point.If the query is simple there is no difference but EVERY extra action even which is supposed to improve performance makes optimizer works harder and optimizer can fail to get the best plan in time and will run not optimal query. So extras select can be a such action which can even decrease performance