When And How To Use CTEs (Common Table Expressions)

Writing SQL queries can feel like solving a puzzle with missing pieces. As datasets grow and queries become more complex, managing subqueries and joins can become a frustrating tangle of code that’s difficult to read, debug, and optimize. 

Common Table Expressions (CTEs) help break down complex queries into clear, reusable parts, making SQL easier to write and maintain. Whether you’re simplifying long-winded subqueries, handling hierarchical data like organizational charts, or replacing temporary tables, CTEs provide a structured approach that improves readability and performance.

But when should you use CTEs, and how do they compare to other SQL techniques? In this guide, we’ll explore how CTEs work, when they’re the best tool for the job, and how to apply them in your queries. By the end, you’ll be able to confidently write cleaner, more efficient SQL.

What are Common Table Expressions?

A CTE is a way to define a temporary result set within a SQL query. It allows you to create a named query that can be referenced multiple times in the main statement, making complex queries easier to read and manage.

CTEs were introduced in SQL-99 and have since been widely adopted across relational databases like PostgreSQL, SQL Server, MySQL (starting from version 8.0), and Oracle. They are also fully supported in modern cloud data platforms, including Snowflake, Databricks, Redshift, and BigQuery, where they help improve query readability and maintainability.

They serve as a structured alternative to subqueries and temporary tables, giving SQL developers a more readable and efficient way to organize their logic.

When to use Common Table Expressions

CTEs aren’t always the right choice, but they shine in certain situations where they improve readability, efficiency, and maintainability. Understanding when to use them ensures your queries remain structured, scalable, and easy to troubleshoot.

Simplifying complex queries

When a query involves multiple layers of subqueries or joins, CTEs help break it down into manageable parts. Instead of dealing with deeply nested logic that’s difficult to follow, a CTE allows you to separate key components into clear, logical sections. This makes debugging and modifying queries much easier, especially when working with large datasets or complex reporting needs.

Improving query readability and reusability

If the same subquery logic is used more than once within a query, a CTE allows you to write it once and reference it multiple times. This eliminates redundancy, reducing the chance of errors and making the query easier to maintain. Instead of copying and pasting the same subquery throughout different parts of the SQL statement, a CTE centralizes that logic, ensuring consistency across your query.

Handling recursive queries

Recursive CTEs provide a structured way to process hierarchical data, such as organizational charts, product categories, or family trees. Without CTEs, querying hierarchical relationships often requires cumbersome self-joins or complex procedural logic. 

A recursive CTE simplifies this by allowing a query to reference itself, iterating through parent-child relationships until the full hierarchy is retrieved. This is particularly useful for database designs that store data in tree-like structures.

Replacing temporary tables

Instead of creating and dropping temporary tables, CTEs allow for similar functionality without the extra management overhead. Temporary tables require explicit creation, take up storage space, and can introduce performance bottlenecks if not properly managed. 

CTEs, on the other hand, are evaluated only during query execution and disappear once the statement completes. This makes them an efficient alternative when working with temporary result sets that don’t need to persist beyond a single query run.

Enhancing performance in certain cases

When used correctly, CTEs can help the query optimizer produce a more efficient execution plan by avoiding redundant calculations. While not a guaranteed performance booster, CTEs can be particularly useful when filtering or aggregating large datasets before they are joined with other tables. By pre-processing data in a structured way, CTEs can reduce the computational workload, leading to faster query execution and better resource utilization.

Understanding their strengths allows you to determine when a CTE is the best tool for structuring your SQL logic.

How Common Table Expressions work in SQL

A Common Table Expression (CTE) creates a temporary result set that can be referenced within a query. Unlike subqueries, which can make SQL harder to read, CTEs allow you to organize complex logic into a structured format.

CTEs use the WITH clause to define a named temporary table. Once defined, this table can be referenced in the main query, just like a standard table or view. Since CTEs exist only for the duration of the query execution, they don’t store data permanently, making them a lightweight and efficient tool for simplifying SQL statements. 

Here’s how CTEs function in a query:

Define the CTE using the WITH clause. This step names the temporary result set and specifies the query that populates it. Then, use the CTE for the main query. The named CTE can be referenced as many times as needed within the same statement. Finally, execute the full query. The database processes the CTE first and then applies the result to the main query.

By structuring queries this way, SQL developers can improve readability and reduce duplication, making their code easier to maintain.

5 benefits of using Common Table Expressions

CTEs solve real challenges in SQL query writing by improving readability, reducing redundancy, and simplifying debugging. They provide a structured way to organize queries, making complex logic easier to manage and maintain.

• Improves readability: Instead of wading through deeply nested subqueries, CTEs break queries into logical steps, making them easier to follow.
• Reduces code duplication: Write a query once and reference it multiple times, avoiding repetitive subqueries.
• Simplifies debugging: With CTEs, troubleshooting becomes more straightforward since each section of the query is isolated and easier to test.
• Handles hierarchical data efficiently: Recursive CTEs are ideal for querying organizational structures, category trees, or other parent-child relationships.
• Replaces temporary tables in many cases: CTEs provide similar functionality without creating and dropping temporary tables, reducing database overhead.

While CTEs won’t always replace subqueries or temporary tables, they offer a cleaner, more efficient approach for many SQL scenarios.

Types of Common Table Expressions

Not all CTEs work the same way. CTEs can serve different purposes in SQL queries depending on how they are structured. Here are the main types you’ll encounter:

Non-recursive CTEs

A non-recursive CTE is the most common type. It retrieves a result set once and uses that data within a query. This is useful for simplifying complex joins, filtering large datasets, or breaking up lengthy SQL statements into readable sections.

Recursive CTEs

A recursive CTE is used when querying hierarchical data, such as employee structures, category trees, or network connections. It references itself within the query, allowing SQL to repeatedly process rows until a stopping condition is met.

Inline and materialized CTEs

Some databases optimize CTEs differently. Inline CTEs are processed like subqueries, meaning the database dynamically evaluates them as part of query execution. Some databases allow CTEs to be temporarily stored (materialized) for performance improvements, reducing redundant calculations. These are called Materialized CTEs.

Understanding these different types helps choose the best approach for efficiently structuring SQL queries.

Basic syntax and structure of a CTE

CTEs provide a structured way to simplify queries by defining a temporary result set that can be referenced multiple times within a statement. Instead of relying on deeply nested subqueries, CTEs allow you to break queries into logical sections, making them easier to read, debug, and maintain.

A CTE begins with the WITH clause, which defines its scope. It is assigned a name that acts as a temporary table for use in the main query. Optionally, column names can be specified within parentheses, which is particularly useful when dealing with calculated fields or when renaming columns for clarity. The subquery inside the CTE generates the result set, and the main query then references this temporary dataset as if it were a standard table.

General syntax of a CTE

The general syntax of a CTE follows a consistent pattern. It starts with the WITH clause, followed by the CTE name, an optional column list, and a subquery that defines the temporary result set. Once defined, the CTE can be used in the main query as if it were a regular table. Here’s the basic structure:

WITH cte_name AS (

    SELECT column1, column2 

    FROM table_name 

    WHERE condition

)

SELECT * FROM cte_name;

CTEs are especially powerful when working with hierarchical data, such as employee reporting structures, product categories, or organizational charts. A recursive CTE allows a query to repeatedly reference itself, making it possible to traverse hierarchical relationships efficiently. Instead of using complex self-joins or procedural loops, a recursive CTE enables a streamlined approach to processing hierarchical data.

The following example retrieves an employee hierarchy, starting from a manager (employee_id = 1) and recursively finding all their direct and indirect reports.

WITH RECURSIVE employee_hierarchy AS (

    -- Anchor member: Select the top-level manager

    SELECT id, name, manager_id, 1 AS level

    FROM employees

    WHERE id = 1 

    UNION ALL

    -- Recursive member: Find employees reporting to the previous level

    SELECT e.id, e.name, e.manager_id, eh.level + 1

    FROM employees e

    INNER JOIN employee_hierarchy eh

    ON e.manager_id = eh.id

)

SELECT * FROM employee_hierarchy

ORDER BY level, id;

This query starts by selecting the top-level manager (employee with id = 1). This initial selection, known as the anchor query, retrieves the highest-level employee in the hierarchy. Next, the recursive query joins the employees table back to itself, retrieving all direct reports of the previously retrieved employees. 

This process continues iteratively, with each level of employees being added until no more direct reports are found. The final result is an ordered list showing employees at different levels of the hierarchy, from the highest-ranking manager to the lowest-level employee.

Using a recursive CTE in this scenario eliminates the need for complex self-joins and procedural loops, making hierarchical queries easier to write and understand. Whether using a non-recursive CTE for query simplification or a recursive CTE for hierarchical processing, CTEs provide a powerful way to structure SQL queries, making them more readable, reusable, and maintainable. By understanding their syntax and purpose, you can improve the efficiency of your SQL queries while keeping them organized and easy to debug.

Writing your first CTE

Now that you understand the structure of a CTE, let’s walk through a real-world example. Imagine you’re analyzing sales performance and need to identify top-performing sales representatives who have generated more than $317,000 in total revenue. 

Without a CTE, you might use a nested subquery, making the SQL harder to read and debug. A CTE simplifies the logic by organizing it into clear, structured steps.

The query below creates a temporary result set of high-performing sales reps and then retrieves their details in the main query.

WITH top_sellers AS (

    SELECT id, name, total_sales, region

    FROM sales_data

    WHERE total_sales > 317000

)

SELECT name, region, total_sales 

FROM top_sellers

ORDER BY total_sales DESC;

This approach makes the query more readable by separating the filtering logic (total_sales > 317000) from the main query. The CTE defines the result set first, allowing the main query to focus only on selecting and sorting the data. If this logic needed to be reused elsewhere in the query, the CTE would eliminate the need to repeat the same filtering conditions multiple times, ensuring cleaner, more maintainable SQL.

CTEs are especially useful in scenarios like this, where filtering, ranking, or aggregating data requires breaking a complex query into manageable steps. Using a CTE makes SQL queries easier to read, modify, and optimize, saving time when working with large datasets.

CTEs’ many benefits

Writing SQL should feel structured and intuitive, unlike unraveling a tangled mess of subqueries. Common Table Expressions (CTEs) are a powerful way to organize query logic, improve readability, reduce redundancy, and make debugging easier. Whether you’re simplifying complex queries, handling hierarchical data, or replacing temporary tables, CTEs help streamline SQL workflows and keep your queries efficient.

Here are some key takeaways:

• CTEs improve query readability by breaking down complex logic into manageable sections.
• They eliminate code duplication, allowing you to reference a result set multiple times.
• Recursive CTEs are ideal for hierarchical data like employee structures and category trees.
• CTEs can replace temporary tables, reducing unnecessary storage overhead.
• While CTEs improve organization, they don’t always guarantee better performance, and optimizing execution plans is key.

Put it into practice

If you’re new to CTEs, start by refactoring an existing SQL query that uses nested subqueries or temporary tables. Try converting it into a non-recursive CTE to improve readability. If your work involves hierarchical data, experiment with a recursive CTE to retrieve structured relationships more efficiently.

Quick reference guide: Writing a CTE

WITH cte_name AS (

    SELECT column1, column2

    FROM table_name

    WHERE condition

)

SELECT * FROM cte_name;

• Use WITH to define the CTE before the main query.
• Reference the CTE in the main query just like a regular table.
• For recursion, include UNION ALL to process hierarchical relationships iteratively.

CTEs are a convenient SQL feature and a smarter way to write queries. The next time you’re working on a complex SQL statement, consider using a CTE to make your code cleaner, easier to debug, and more efficient.

Common Table Expression frequently asked questions

Even with a solid understanding of CTEs, you might still have some questions about when and how to use them. Here are some of the most common ones:

What is the difference between a CTE and a subquery?

A CTE improves readability by breaking complex queries into named sections, while a subquery is embedded directly in the SQL statement. CTEs are often easier to debug and reuse, making them a preferred choice for structuring queries.

When should I use a recursive CTE?

Recursive CTEs are useful for hierarchical data, such as organizational charts, product categories, or connected networks. If you need to process data that has parent-child relationships, a recursive CTE can help retrieve and structure it efficiently.

Do CTEs always improve performance?

Not necessarily. While CTEs can help with readability and organization, performance benefits depend on how the database engine processes them. Some databases optimize CTEs like subqueries, while others materialize them, affecting execution speed.

Are CTEs supported in all SQL databases?

Most modern relational databases, including PostgreSQL, MySQL (starting from version 8.0), SQL Server, and Oracle, support CTEs. However, older versions of some databases may not include full CTE functionality, especially for recursion.

Can I use multiple CTEs in a single query?

Yes, you can define multiple CTEs within the same query using a comma-separated list. This approach is useful for breaking down even more complex queries into distinct, logical steps.