CTE vs Subquery vs Temp Table

Concepts covered: sqlCte

On modern engines, the default behavior for a CTE is to inline it into the surrounding query. The optimizer treats the CTE as if it were a nested subquery in the FROM clause and applies the same optimizations: predicate pushdown, join reordering, common subexpression elimination. Most of the time this is what you want. There is one case where it bites, and one syntactic tool to force the engine's hand. When inlining helps and when it hurts Inlining helps in the common case: a CTE that filters or shapes data, referenced once in the final SELECT. The optimizer pushes the WHERE clause from the outer query into the CTE's body, evaluates the combined predicate against the source, and reads only the rows it needs. The CTE is a readability wrapper; the cost equals an equivalent subquery. Inlining

About This Interactive Section

This section is part of the CTEs (Common Table Expressions): Intermediate lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.