What Is a CTE in SQL?

What Is a CTE in SQL?

A Common Table Expression (CTE) is a temporary named result set created with the WITH clause and referenced like a table in the same statement.

Why Use a CTE Instead of Subqueries?

CTEs improve readability because you assign a name to each logical step, avoiding deeply nested subqueries that are hard to parse.

CTEs can be referenced multiple times in the outer query, preventing duplication and making maintenance easy when the business rule changes.

Database optimizers usually inline CTE logic, so performance is comparable to equivalent subqueries in most engines.

How Do You Write a Basic CTE?

Start with WITH, provide a CTE name, list column aliases optionally, and supply a SELECT statement.

WITH recent_orders AS (
SELECT order_id, customer_id, total_cents
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
)
SELECT *
FROM recent_orders;


The CTE recent_orders exists only during this query execution and cannot be reused afterward.

Can You Chain Multiple CTEs?

You can declare multiple CTEs by separating them with commas, allowing each to build on the previous.

WITH recent_orders AS (
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
),
order_totals AS (
SELECT customer_id, SUM(total_cents) AS spend_cents
FROM recent_orders
GROUP BY customer_id
)
SELECT *
FROM order_totals
ORDER BY spend_cents DESC;


This pattern keeps complex logic modular and traceable.

What Is a Recursive CTE?

A recursive CTE repeatedly executes until no new rows appear, making it ideal for hierarchical data like org charts or folder trees.

WITH RECURSIVE org_tree AS (
SELECT id, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, ot.level + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;


The anchor query seeds the recursion; UNION ALL adds children until the hierarchy is exhausted.

When Should You Avoid Recursive CTEs?

Skip recursion if the depth is unknown and large, as it may cause many iterations and memory use; iterative solutions or adjacency lists might be faster.

Do CTEs Persist Like Temporary Tables?

CTEs live only during the statement’s execution, whereas temporary tables persist for the session or transaction; use temporary tables for cross-statement reuse.

How Do CTEs Compare to Views?

Views are stored database objects reusable by any session, but CTEs are ad-hoc and scoped to one statement. Use views for enterprise-wide definitions and CTEs for one-off transformations.

Can You Index a CTE?

You cannot create indexes on a CTE because it is not materialized as a real object; rely on indexes defined on the underlying tables.

What Are Best Practices for Writing CTEs?

Give meaningful names that explain the CTE’s role, such as filtered_orders or daily_totals.

Place complex calculations inside CTEs to isolate business logic and keep SELECT clauses concise.

Avoid unnecessary CTE layering; each CTE should add clear value or be removed.

How Do You Debug CTEs?

Run each CTE SELECT individually to validate row counts, filters, and joins before chaining them.

Use EXPLAIN to confirm the optimizer inlines the CTE and does not create temporary materializations that might slow the query.

Can You Update Data Using a CTE?

Yes. Many databases support UPDATE or DELETE statements that reference a CTE.

WITH inactive_users AS (
SELECT user_id FROM users WHERE last_login < CURRENT_DATE - INTERVAL '1 year'
)
DELETE FROM users
WHERE user_id IN (SELECT user_id FROM inactive_users);


This pattern makes set-based modifications readable and safe.

How Do Database Engines Optimize CTEs?

Most optimizers inline non-recursive CTEs into the main query, so you rarely pay extra cost. Recursive CTEs may spool intermediate rows.

What Real-World Problems Do CTEs Solve?

CTEs simplify time-window analyses, cohort retention reports, running totals, and hierarchical reports, making them a daily tool for data engineers.

Key Takeaways on SQL CTEs

CTEs are temporary named subqueries that increase readability, enable reuse, and support recursion. Mastering CTE syntax and best practices yields cleaner, maintainable SQL.