Common Table Expressions (CTEs)

Common table expressions (CTEs) name intermediate query results within a SQL statement, helping organize complex queries. Recursive CTEs can express iterative traversals, while CTE materialization behavior depends on the database.

A common table expression (CTE) is a named query result introduced with `WITH`. The main statement can refer to it as though it were a relation, making multi-step SQL easier to organize and review.

A statement may define multiple CTEs, separated by commas. Later CTEs can refer to earlier ones, and the final `SELECT`, `INSERT`, `UPDATE`, or `DELETE` can use them. Their scope is the statement containing the `WITH` clause.

A CTE is a way to structure a query, not a promise that the database will store intermediate rows. Whether a CTE is inlined or materialized depends on the database and, in some systems, query details or explicit syntax. Check the execution plan when performance matters.

Recursive CTEs use `WITH RECURSIVE` in systems that support this syntax. They combine an initial result (the anchor) with repeated recursive steps, commonly to traverse trees or graphs. Termination conditions and cycle handling are important to avoid unbounded recursion.

Key points

• CTEs name intermediate query results using `WITH`.
• A CTE's name is available within its containing statement.
• Later CTEs can build on earlier CTEs in the same clause.
• CTEs improve query organization but do not guarantee physical materialization.
• Recursive CTEs support iterative queries such as hierarchy traversal.

Interview questions

What is a CTE, and when might you use one?

A CTE is a named query result defined with `WITH` and available to the statement that defines it. It can make a large query easier to read by separating stages into named pieces. It does not inherently mean the result is physically stored. Its execution and optimization depend on the database.

How do you organize a query that has several dependent transformation steps?

Multiple CTEs can appear in one `WITH` clause, with commas between their definitions. A later CTE can use an earlier CTE, which lets you express a pipeline of query steps. The final statement can then use one or more of those names. Keep dependencies clear, since the definitions are still part of one statement rather than independently reusable objects.

How does a recursive CTE work, and what problems can arise when using one?

A recursive CTE builds a result iteratively from an anchor query and a recursive query that refers to the CTE. It is useful for hierarchical data such as organizational reporting lines or category trees. The recursive step needs a condition that eventually stops producing rows. For graph-like data, consider whether duplicate paths or cycles need explicit handling.

Does a CTE always materialize, and how would you reason about its performance?

No: a CTE primarily names and structures a query expression. Some database systems or query forms may materialize a CTE, while others may inline it into the surrounding query. These choices can affect optimization, but the precise rules are database-specific. Inspect the execution plan and consult the relevant database documentation before relying on a performance assumption.