I’m seeing a lot of debate about Common Table Expressions (CTEs) vs. Subqueries. My colleagues say CTEs are just "syntactic sugar" and can actually be slower in certain SQL dialects. For complex multi-step data transformations, which approach is preferred for modern data analysis?
3 answers
In 2025, the "readability is king" rule favors CTEs. They allow you to structure your query like a narrative, which is vital for team collaboration. Regarding performance, most modern optimizers (PostgreSQL 12+, SQL Server, BigQuery) treat non-recursive CTEs the same as subqueries. However, in older versions of Postgres, CTEs acted as optimization fences, which could slow things down. My advice: use CTEs for anything with more than two steps. The 0.5% performance hit is worth the hours saved in debugging and maintaining "spaghetti" subqueries later on.
Have you tried using the EXPLAIN ANALYZE command on both versions of your query to see if your specific SQL engine is materializing the CTE?
CTEs are much easier to read. If you're doing complex data analysis, do your future self a favor and stop nesting subqueries four levels deep.
Absolutely. Plus, CTEs make it so much easier to test individual parts of your logic by simply changing the final SELECT statement.
I did run an EXPLAIN plan! It looks like my SQL Server instance is inlining the CTE anyway, so the performance is identical. However, when I used a CTE twice in the same query, it seemed to calculate it twice instead of caching it. Is there a specific keyword I should use to force the engine to cache the result set, or is that strictly up to the engine's internal optimizer?