What Is a SQL CTE?

What Is a SQL CTE?

A Common Table Expression (CTE) is a temporary, named result set defined with the WITH keyword that exists only for the duration of a single query. Databases treat a CTE like an inline view, letting you reference it multiple times without duplicating logic.

Why Use a CTE Instead of a Subquery?

CTEs improve readability by giving the subquery a descriptive name, reduce duplication when the same derived table is needed more than once, and enable recursion—something regular subqueries cannot do.

How Does CTE Syntax Work?

Place WITH cte_name AS (SELECT ...) before the main SELECT, UPDATE, INSERT, or DELETE statement. You can define multiple CTEs by separating them with commas.

Can I Chain Multiple CTEs?

Yes. Each CTE can reference earlier CTEs in the same WITH clause, allowing you to build complex logic step-by-step while keeping each step short and clear.

How Long Does a CTE Live?

A CTE lives only for the duration of the single statement that follows the WITH clause. After execution, it disappears and consumes no additional memory or storage.

Does a CTE Improve Performance?

CTEs themselves do not guarantee faster queries. They can reduce duplication, which may help the optimizer, but performance depends on execution plans. Treat CTEs as a readability tool first.

What Is a Recursive CTE?

A recursive CTE references itself to iterate over hierarchical data such as org charts or folder trees. It consists of an anchor query that sets the starting rows and a recursive query that repeatedly joins back to the CTE.

When Should I Use a Recursive CTE?

Use recursive CTEs when you need to traverse parent-child relationships of unknown depth, build running totals, or generate sequences without stored procedures.

What Are Common CTE Use Cases?

Typical use cases include simplifying multi-step aggregations, reusing complex joins across several parts of a query, isolating business logic, performing recursion, and preparing data for window functions.

How Do I UPDATE With a CTE?

Most relational databases allow UPDATE, DELETE, or INSERT operations that target the rows returned by a CTE, offering a concise, set-based alternative to cursors.

Best Practices for Writing CTEs

Name each CTE descriptively, keep columns minimal, avoid nesting CTEs too deeply, and test performance by comparing CTEs versus inline subqueries or temp tables.

Is a CTE the Same as a Temp Table?

No. A CTE is virtual and query-scoped, while a temporary table is physically materialized and persists for the session unless dropped. Choose temp tables when you need indexes or repeated access across multiple queries.

How Does a CTE Interact With Window Functions?

CTEs can stage pre-aggregated or filtered data, making subsequent window functions simpler and faster to read by separating concerns into logical steps.

Which Databases Support CTEs?

PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite, Snowflake, BigQuery, and most modern SQL engines support both non-recursive and recursive CTEs.

Wrap-Up: Key Takeaways

CTEs create named, query-scoped result sets that clarify logic and enable recursion. Use them to avoid duplication, enhance maintainability, and structure complex queries into readable steps. Test performance and follow naming conventions for maximum benefit.