Run subquery once / optimize join with subquery without needing to use temporary table
I was under the impression that when you have a subquery in a join like this the SQL engine would get the data first before joining it with the outer query.


The query optimizer considers various physical execution strategies that are guaranteed to produce the same results as the original logical query specification. So, no, there is no general inference you can make from the written form of the query about how it will actually be executed.

The decision the optimizer makes is based on estimated costs, which are determined to a large extent by the size (cardinality and width) of the data expected at each stage of the pipeline, and the type of physical operation performed.

If your query happens to result in inaccurate cost estimates, the strategy chosen by the optimizer is quite likely not to be optimal, perhaps not even very good at all.

Inaccurate costs have many causes, including unrepresentative statistics on the base objects, and the use of opaque, or difficult to estimate, predicates in the query. With good statistics, a reasonable sized query, simple comparison operations, and a relational schema, the chances are quite good that estimates (and hence the optimizer's physical plan selection) will be at least good enough.

One way to simplify the optimizer's work is to materialize parts of a complex query (or one that simply does not get good estimates) into temporary tables. These give the optimizer direct cardinality information, and SQL Server can usually automatically create statistics on the temporary table to provide further good information. Last, but not least, the temporary table allows you to add specific indexes to it, where that is worthwhile.

When used sensibly*, temporary tables are a great way to provide information to the optimizer, producing reliable execution plans even as the distribution and size of the underlying data changes.

If you have done all you can with statistics, indexing, and the written form of the query, using a temporary table might well be optimal here. It's impossible to say for sure, based on the information given in the question, and not knowing the expertise level of the author.

* Sensible use means e.g. limiting the number, size, and width of temporary tables required, as well as the frequency of use. SELECT * INTO #temp FROM dbo.MyHugeTable; is not sensible, for example.