In Memory SQL queries are parsed[1] and placed into C# code segments which are then compiled in classes and executed in parallel with each other, maximizing performance through the utilization of all available CPU cores.
Further efficiency is achieved by reusing classes whenever possible across any number of queries. Constants, for example, would be changed into parameters in the class. Also field names and table names would be generalized parameters, so the same class could execute against many different tables.
The query compiler also works recursively, turning sub-queries into classes as well.
By default, a sub-query creates an internal temporary table. This helps with performance because the same sub-queries tend to be generated a lot. To disable this feature, the keyword NOCACHE may be placed after Select in the Inner SQL.
Instead of caching a sub-query table, the outer WHERE clause conditions are applied, where applicable. Furthermore only the required columns of the parent query are generated in the sub-query, saving more time, space and processing power.
For some queries compiling is not required. For example, if a query is performing a Count Distinct only, then this information is stored directly in the database as the cardinality of the lookup table.
[1] SQL parsing is aided by use of an open source technology called ANTLR.