How do CTEs compare to temporary tables?

A CTE exists only within a single query statement and requires zero cleanup DDL. A Temporary Table physically persists across the entire database session, can be indexed, and is ideal when a large intermediate result set is queried repeatedly across multiple separate queries.

What was the 'CTE optimization fence' in older PostgreSQL versions?

Prior to PostgreSQL 12, PostgreSQL always materialized CTEs as an opaque boundary. The optimizer could not push down WHERE filters or index constraints into the CTE from the outer query. In PostgreSQL 12+, CTEs are inlined by default unless explicitly declared `WITH cte AS MATERIALIZED (...)`.

What is a data-modifying CTE?

In PostgreSQL, a CTE can execute INSERT, UPDATE, or DELETE statements with a `RETURNING` clause. This allows moving or archiving rows in a single atomic statement: `WITH moved AS (DELETE FROM active RETURNING *) INSERT INTO archive SELECT * FROM moved;`.

Can multiple CTEs reference each other in the same query?

Yes! You can chain multiple comma-separated CTEs in a single query. Each subsequent CTE can reference any previously defined CTE in the list, enabling linear, step-by-step pipeline transformations.

SQL Formatting

The Master Guide to SQL Common Table Expressions (CTEs): Syntax, Optimization Fences, and Recursion

By Sharon Ben-Moshe · October 13, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

The definitive guide to SQL Common Table Expressions (CTEs). Master chaining, recursive hierarchies, data-modifying CTEs, and optimization fences.

Quick answer

A Common Table Expression (CTE) is a named temporary result set defined using the WITH clause that exists only for the duration of a single SQL statement. CTEs improve readability over nested subqueries, can be referenced multiple times, support recursion for hierarchical data, and can be explicitly materialized or inlined by query optimizers.

The Master Guide to SQL Common Table Expressions (CTEs): Syntax, Optimization Fences, and Recursion

Quick answer

FAQs

How do CTEs compare to temporary tables?

What was the 'CTE optimization fence' in older PostgreSQL versions?

What is a data-modifying CTE?

Can multiple CTEs reference each other in the same query?

Building an Enterprise SQL Style Guide: Formatting Standards, Linting, and Team Conventions

How to Format Recursive CTEs in SQL: Hierarchical Query Structure

Formatting SQL Parentheses: Boolean Logic Precedence and Readability

Key takeaways

Chaining multi-step analytical CTEs

Data-modifying CTEs: Atomic move and archive

Official references

Quick answer

FAQs

How do CTEs compare to temporary tables?

What was the 'CTE optimization fence' in older PostgreSQL versions?

What is a data-modifying CTE?

Can multiple CTEs reference each other in the same query?

Keep reading

Building an Enterprise SQL Style Guide: Formatting Standards, Linting, and Team Conventions

How to Format Recursive CTEs in SQL: Hierarchical Query Structure

Formatting SQL Parentheses: Boolean Logic Precedence and Readability

Key takeaways

Chaining multi-step analytical CTEs

Data-modifying CTEs: Atomic move and archive

Related SQL guides

Official references