For the uninitiated, a derived table is where you create a query that contains another query but that second query is wrapped in parenthesis and you will normally join to it for further results or set operations.

The pseudo-code is like:

Select a.Blah, b.BlahBlah
From  FirstTable a
Join (select BlahBlah from SecondTable ) b On b.ID = a.ID

So the second query becomes the “derived table”, all in memory, and is joinable just like a regular table, and all of its indexes are play as well.

BUT - do derived tables have performance better or worse than temp tables and in memory temp table variables?