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.

TASK

Use CTEs for readable SQL

Common Table Expressions (WITH clause) create named temporary result sets, improving readability and 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

SQL Formatter →
Format 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
View recipe Open in Toolchain

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.

Frequently asked questions

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.