Subqueries vs CTEs: Query Optimizer Internals & Memory Spooling Explained

Many engineers think Common Table Expressions (CTEs) always outperform subqueries. However, in recent versions of SQL Server and PostgreSQL, this is not the case. The SQL optimizer treats standard CTEs and derived tables (subqueries) in almost the same way: both are expanded into the same relational tree, yielding identical execution plans and I/O costs.

Pattern A shows a derived table (subquery) that ranks employees by salary within each department, selecting the second-highest salary. Pattern B illustrates a similar query using a Common Table Expression (CTE) to achieve the same result.

CTEs offer advantages in terms of readability and pipeline stacking. You can string together multiple CTEs sequentially without deeply nested brackets. Furthermore, in SQL Server, you can perform DELETE directly on a CTE, deleting duplicate rows straight from the underlying table. The example shows how to clean up duplicate customer records in-place.

However, there is a significant catch: if a CTE is referenced more than once in a query (e.g., joining a CTE to itself), SQL Server may execute the query multiple times or create a Lazy Spool in tempdb. To avoid this performance overhead, especially when dealing with heavy multi-million row reuse, it is recommended to use a Temporary Table ( #TempTable ) with an explicit Clustered Index instead.