Are CTEs materialized?

In PostgreSQL <12, CTEs are always materialized (computed once, stored). In PostgreSQL 12+, they are inlined by default (like subqueries) unless MATERIALIZED is specified. In SQL Server and MySQL, CTEs are inlined. Materialization helps when the CTE is referenced multiple times; inlining helps when the optimizer can push down predicates.

Can CTEs replace temporary tables?

For single-query use, yes — CTEs are cleaner. For multi-statement use (multiple queries referencing the same data), use temporary tables — CTEs only exist within one statement. Temporary tables can also be indexed; CTEs cannot.

Utiliser CTEs pour readable SQL

Commun Table Expressions (WITH clause) créer named temporary résultat sets, improving readability et enabling recursion.

1

A CTE is defined with WITH name AS (SELECT ...) and referenced like a table. Multiple CTEs: WITH a AS (...), b AS (...) SELECT .... CTEs are evaluated once per query (unlike subqueries which may be evaluated multiple times). Recursive CTEs (WITH RECURSIVE) can reference themselves for hierarchical queries.
2

Formateur SQL →
Formater CTE queries to see each named result set clearly. Good formatting aligns the WITH keyword, comma-separated CTE names, and the main query. This makes complex queries readable and maintainable.
3

sql-format → word-count
Voir la recette Ouvrir dans la chaîne d’outils

Use the SQL format workflow to format and document CTE queries. This produces clean SQL with comments explaining each CTE's purpose, making the query self-documenting.

Questions fréquentes

Are CTEs materialized?: In PostgreSQL <12, CTEs are always materialized (computed once, stored). In PostgreSQL 12+, they are inlined by default (like subqueries) unless MATERIALIZED is specified. In SQL Server and MySQL, CTEs are inlined. Materialization helps when the CTE is referenced multiple times; inlining helps when the optimizer can push down predicates.
Can CTEs replace temporary tables?: For single-query use, yes — CTEs are cleaner. For multi-statement use (multiple queries referencing the same data), use temporary tables — CTEs only exist within one statement. Temporary tables can also be indexed; CTEs cannot.