Common Table Expressions (CTEs) Flashcards

When should you prefer a CTE over a view?

Answer: For a one-off query where you do not need a reusable database object

CTEs suit single-query use, while views are better for logic reused across many queries.

In some databases, a CTE result referenced multiple times in a query may be:

Answer: Re-evaluated each time unless materialized

Many engines re-evaluate a CTE per reference unless it is explicitly or implicitly materialized.

What PostgreSQL keyword can force a CTE to be computed once and stored?

Answer: MATERIALIZED

PostgreSQL supports WITH cte AS MATERIALIZED (...) to force single evaluation.

Can a CTE be referenced in the WHERE clause of an outer query as a table source directly?

Answer: No, it is referenced in FROM or JOIN like a table, then filtered

A CTE is used as a table source in FROM or JOIN, and filtering happens via WHERE on that source.

Which is a valid reason to use a CTE for an UPDATE statement in PostgreSQL?

Answer: To compute rows to update in a readable, staged way

A CTE can pre-compute the target rows or values, making complex UPDATE logic clearer.

What happens to column names in a CTE if you do not specify them explicitly?

Answer: They are inherited from the CTE's SELECT list

Without an explicit column list, the CTE inherits column names from its inner SELECT.

Why can a CTE improve maintainability of a complex aggregation query?

Answer: It breaks the logic into named, sequential steps

CTEs let you decompose complex logic into named steps that are easier to read and maintain.