Where are the results from subqueries stored? Are temp tables in tempdb created? I have not been able to find any documentation regarding this...thank!
It depends on what the query engine decides. If it may be lengthy and not fit in memory it will write out to a tabl in TempDB. If it can survive in memory it will remain memory resident and attached too. Other conditions of the query engine trying to optimize putting the data together may cause it to use tempdb.