Editorial Team · on 13 June 2026 · 7 min read · Last reviewed 13 June 2026

Common Table Expressions (CTEs) in SQL are temporary result sets that simplify complex queries by breaking them into smaller, more manageable parts.

Key facts

• CTEs are defined using the WITH clause and can be referenced within the same query.
• They improve query readability and maintainability by structuring complex logic.
• CTEs can be recursive, allowing for hierarchical data processing.
• They are widely supported across SQL databases, including PostgreSQL, SQL Server, Oracle, and MySQL (version 8.0+).

What are the basic syntax and structure of CTEs?

A CTE is defined using the WITH clause followed by a name for the CTE, an optional column list, and the query that produces the result set. The main query references the CTE name as a table. Here is the basic syntax:

Component	Description
WITH	Keyword that starts the CTE definition.
CTE_name	The name given to the CTE, which is used later in the query.
AS	Keyword that separates the CTE name from the query defining it.
Query	The SQL query that generates the result set for the CTE.
Main_query	The primary query that references the CTE.

The basic structure of a CTE is as follows:

WITH CTE_name AS (
    -- CTE query defining the result set
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- Main query referencing the CTE
SELECT * FROM CTE_name
WHERE some_condition;

For example, consider a query that retrieves employees and their departments. Without a CTE, the query might look like this:

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

Using a CTE, the same query can be broken down for better readability:

WITH Sales_Employees AS (
    SELECT e.employee_name, e.department_id
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = 'Sales'
)
SELECT se.employee_name, d.department_name
FROM Sales_Employees se
JOIN departments d ON se.department_id = d.department_id;

How do CTEs improve query readability and maintainability?

CTEs enhance readability by allowing developers to break down complex queries into simpler, logical parts. This modular approach makes the query easier to understand and debug. For instance, a query with multiple joins and subqueries can be simplified using CTEs, as shown in the previous example.

Maintainability is improved because CTEs allow developers to give meaningful names to intermediate results. This makes the query self-documenting and easier to update. When changes are needed, modifying a CTE is often simpler than modifying a nested subquery. Additionally, CTEs can be reused within the same query, reducing redundancy and improving consistency.

For example, consider a scenario where you need to calculate the average salary by department and then find departments with above-average salaries. Without CTEs, this query might be complex and hard to read. With CTEs, it becomes more structured and easier to understand:

WITH Dept_Avg_Salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
Above_Avg_Departments AS (
    SELECT department_id
    FROM Dept_Avg_Salary
    WHERE avg_salary > (SELECT AVG(avg_salary) FROM Dept_Avg_Salary)
)
SELECT d.department_name
FROM departments d
JOIN Above_Avg_Departments aad ON d.department_id = aad.department_id;

Can CTEs be recursive, and if so, how are they used?

Yes, CTEs can be recursive, allowing for the processing of hierarchical data. A recursive CTE references itself, enabling the traversal of tree-like structures, such as organizational charts or bill of materials. The syntax for a recursive CTE includes a base case (the initial query) and a recursive case (the part that references the CTE itself).

The basic structure of a recursive CTE is as follows:

WITH RECURSIVE CTE_name AS (
    -- Base case: Initial query defining the starting point
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition

    UNION [ALL]

    -- Recursive case: Query referencing the CTE itself
    SELECT column1, column2, ...
    FROM table_name t
    JOIN CTE_name c ON t.parent_id = c.id
)
-- Main query referencing the recursive CTE
SELECT * FROM CTE_name;

For example, consider a query that retrieves the entire hierarchy of employees under a specific manager. The recursive CTE would start with the manager and then recursively include all their subordinates:

WITH RECURSIVE Employee_Hierarchy AS (
    -- Base case: Start with the manager
    SELECT id, employee_name, manager_id
    FROM employees
    WHERE employee_name = 'John Doe'

    UNION ALL

    -- Recursive case: Include all subordinates
    SELECT e.id, e.employee_name, e.manager_id
    FROM employees e
    JOIN Employee_Hierarchy eh ON e.manager_id = eh.id
)
SELECT employee_name
FROM Employee_Hierarchy;

How do CTEs compare to temporary tables and subqueries?

CTEs, temporary tables, and subqueries are all used to simplify complex queries, but they have different characteristics and use cases. Here is a comparison:

Feature	CTEs	Temporary Tables	Subqueries
Scope	Limited to the query in which they are defined.	Exist for the duration of the session or transaction.	Limited to the query or expression in which they are defined.
Performance	Can be optimized by the query planner, similar to subqueries.	May have better performance for complex, repeated operations due to their persistence.	Can be less efficient for complex operations due to nesting.
Readability	Improve readability by breaking down complex queries into named parts.	Can improve readability but require separate DDL statements.	Can reduce readability due to nesting.
Reusability	Can be referenced multiple times within the same query.	Can be referenced multiple times within the same session.	Limited to a single reference within the query.

In general, CTEs are preferred for improving query readability and maintainability, especially for complex queries. Temporary tables are useful for operations that need to be performed multiple times within a session. Subqueries are suitable for simple, one-time operations within a query.

What are some best practices for using CTEs effectively?

The following best practices can help developers use CTEs effectively:

1. Use descriptive names for CTEs to make the query self-documenting.
2. Break down complex queries into smaller, logical parts using CTEs.
3. Use recursive CTEs for hierarchical data processing.
4. Avoid using CTEs for simple queries where a subquery would suffice.
5. Test the performance of CTEs compared to alternative approaches, such as temporary tables or subqueries.
6. Use CTEs to simplify joins and reduce redundancy in the main query.

For example, consider a query that retrieves customer orders and calculates the total order value for each customer. Using a CTE, the query can be broken down into logical parts:

WITH Customer_Orders AS (
    SELECT customer_id, order_id, order_date, amount
    FROM orders
    WHERE order_date > '2023-01-01'
),
Customer_Totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM Customer_Orders
    GROUP BY customer_id
)
SELECT c.customer_name, ct.total_amount
FROM customers c
JOIN Customer_Totals ct ON c.customer_id = ct.customer_id
WHERE ct.total_amount > 1000;

This approach improves readability and maintainability by breaking down the complex query into smaller, named parts.

What are some common pitfalls to avoid when using CTEs?

While CTEs are powerful tools, there are some common pitfalls to avoid:

• Avoid overusing CTEs for simple queries, as this can make the query more complex than necessary.
• Be mindful of performance implications, as CTEs are not always optimized the same way as temporary tables or subqueries.
• Avoid using CTEs for operations that require persistence beyond the current query, as they are temporary and exist only for the duration of the query.
• Ensure that recursive CTEs have a clear termination condition to avoid infinite loops.
• Avoid using CTEs in databases that do not support them, such as older versions of MySQL.

For example, using a CTE for a simple query that retrieves a single column from a table might not be necessary and could make the query more complex:

-- Unnecessary use of CTE
WITH Simple_CTE AS (
    SELECT column1
    FROM table_name
)
SELECT * FROM Simple_CTE;

In this case, a simple subquery would be more appropriate:

-- Simpler alternative
SELECT column1
FROM table_name;

By following best practices and avoiding common pitfalls, developers can use CTEs effectively to simplify complex queries and improve readability.

Use CTEs to simplify complex SQL queries

CTEs are a powerful tool in SQL that can simplify complex queries, improve readability, and enhance maintainability. By breaking down complex queries into smaller, named parts, CTEs make the query easier to understand and debug. They can also be used for hierarchical data processing through recursive CTEs. However, it is essential to use CTEs judiciously and avoid common pitfalls to ensure optimal performance and readability. For more advanced SQL techniques, explore Mastering Advanced SQL Techniques for Developers and Data Analysts, Optimizing SQL Queries for Performance, and Understanding and Using SQL Window Functions.

Frequently asked questions

What is a Common Table Expression (CTE) in SQL?

A CTE is a temporary result set defined within a SQL query. It starts with the WITH clause and improves query readability by breaking complex queries into simpler parts. CTEs are useful for recursive queries, such as hierarchical data retrieval. They do not store data permanently but exist only for the query's duration.

How do CTEs improve query readability?

CTEs segment complex queries into logical, named blocks. For example, instead of nesting subqueries, you can define intermediate steps as separate CTEs. This makes the query easier to understand and maintain. CTEs also allow referencing the same result set multiple times, reducing redundancy.

Can CTEs be used recursively?

Yes. Recursive CTEs process hierarchical data, like organizational charts or tree structures. They reference themselves, combining base cases with recursive cases. For instance, a recursive CTE can traverse all levels of a category hierarchy, starting from a root category and drilling down to subcategories.

What are the performance considerations for CTEs?

CTEs can enhance performance by simplifying query logic, but they are not always faster than subqueries. The query optimizer treats CTEs similarly to derived tables. For complex operations, test both approaches. Recursive CTEs may be less efficient for deep hierarchies, so consider alternative methods like stored procedures for large datasets.