Questions: Common Table Expressions (CTEs): WITH Clause

Question 1

A developer writes a CTE that performs an expensive aggregation across millions of rows. They reference this CTE three times in the main query, expecting it to execute only once and cache its results. This expectation is:

• A. Correct — CTEs are always materialized and their results cached for reuse within the query
• B. Incorrect — CTEs are typically not materialized; each reference may re-execute the underlying query
• C. Correct, but only in PostgreSQL databases where CTEs are optimized automatically
• D. Incorrect — CTEs always execute more times than subqueries due to parsing overhead

Answer: (B)

CTEs are primarily a readability and maintainability tool, not a performance optimization. Most databases treat a CTE like an inline view — substituting its definition wherever it is referenced and optimizing the combined query. Referencing a CTE three times may cause the underlying query to execute three times. For true materialization (compute once, reuse result), you need an explicit MATERIALIZED hint (PostgreSQL 12+), a temporary table, or a materialized view. The common misconception — that CTEs automatically cache results — can lead to serious performance problems on expensive computations.

Question 2

What is the primary advantage of using a CTE over an equivalent nested subquery?

• A. CTEs always execute faster because the database can optimize them separately from the main query
• B. CTEs eliminate the need to create indexes on intermediate result sets
• C. CTEs let the query logic read top-to-bottom with named steps, rather than inside-out through nested parentheses
• D. CTEs permanently store intermediate results for use in future queries

Answer: (C)

The core benefit of CTEs is structural clarity. A nested subquery forces readers to parse inside-out — the innermost SELECT must be understood before the outer query makes sense. A CTE externalizes each step, names it, and lets the reader follow the logic sequentially: 'first compute X, then Y, then filter using both.' This is especially valuable for complex multi-step queries. Options A and D are wrong: CTEs have no guaranteed performance advantage over subqueries, and they do not persist between queries.

Question 3

In a SQL WITH clause containing multiple CTEs, each CTE can reference CTEs that were defined earlier in the same WITH clause.

• T. True
• F. False

Answer: True

This is one of the most powerful features of CTEs: they can be chained. The syntax is WITH step1 AS (...), step2 AS (SELECT ... FROM step1), step3 AS (SELECT ... FROM step2) SELECT ... FROM step3. Each CTE in the list can reference any previously defined CTE, building a pipeline of transformations. This allows complex multi-stage data transformations to be expressed as a clean sequence of named steps rather than a deeply nested single query.

Question 4

Using a CTE is typically preferable to creating a temporary table when an intermediate result needs to be referenced multiple times in a query.

• T. True
• F. False

Answer: False

For performance-critical work involving large intermediate results referenced multiple times, a temporary table is often better because it is explicitly materialized — computed once and stored. Since CTEs are typically re-executed on each reference, a CTE referenced three times over a large dataset may be three times more expensive than a temporary table. CTEs win on simplicity and readability for moderately complex queries; temporary tables win when materialization matters for performance or when intermediate results must be indexed.

Question 5

Explain the difference between a CTE and a subquery. In what scenario would you prefer a CTE even if their performance is identical?

Model answer: A subquery is a SELECT statement nested inside another query, read inside-out. A CTE is a named temporary result set defined before the main query with the WITH keyword, read top-to-bottom. Their logical results are equivalent — they can often be rewritten as each other — but CTEs are preferable when: (1) the same intermediate result is referenced multiple times, avoiding duplication; (2) the query has multiple logical stages that benefit from named intermediate steps; (3) readability and maintainability matter, since CTEs make query intent explicit.

The key is that CTEs are a communication tool as much as a technical one — they document the intent behind each step of a complex query. A deeply nested subquery may be logically correct but opaque; a chain of well-named CTEs reads like a description of the transformation pipeline. In production environments where queries are maintained over time, this clarity has real value even when performance is equivalent.