Common Table Expressions (CTE) in SQL : A in-depth Guide

1. Reduce Query Complexity

Complex SQL queries often combine multiple joins, filters, aggregations, and conditional logic into a single statement. When all of this logic is written inline, the query quickly becomes difficult to read, reason about, and maintain especially as business requirements evolve.

CTEs help reduce query complexity by allowing you to break a large query into logical, well-defined steps. Each step represents a specific transformation or calculation, making the overall flow of the query easier to follow. Instead of trying to understand everything at once, you can read the query step by step, just like a sequence of operations.

By isolating intermediate results into clearly named CTEs, complex SQL becomes more approachable, more maintainable and far less error-prone without changing the underlying logic or results.

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IN (
    SELECT department_id
    FROM employees
    WHERE salary > 50000
    GROUP BY department_id
    HAVING COUNT(*) > 3
)
GROUP BY d.department_name;

WITH high_salary_departments AS (
    SELECT department_id
    FROM employees
    WHERE salary > 50000
    GROUP BY department_id
    HAVING COUNT(*) > 3
)
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN high_salary_departments h
    ON e.department_id = h.department_id
GROUP BY d.department_name;

2. Replace Deeply Nested Subqueries

Deeply nested subqueries are one of the main reasons SQL queries become difficult to read and maintain. When subqueries are embedded inside SELECT, WHERE or FROM clauses, the logical flow of execution is no longer obvious. Readers must mentally unpack multiple layers of SQL just to understand what the query is doing.

CTEs solve this problem by flattening the query structure. Instead of nesting logic inside parentheses, CTEs move that logic into clearly named, top-level blocks, making the intent of each step explicit.

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_id IN (
        SELECT department_id
        FROM employees
        WHERE salary > (
            SELECT AVG(salary)
            FROM employees
        )
    )
);

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
),
high_paid_employees AS (
    SELECT department_id
    FROM employees, avg_salary
    WHERE salary > avg_sal
),
target_departments AS (
    SELECT department_id
    FROM departments
    WHERE department_id IN (
        SELECT department_id
        FROM high_paid_employees
    )
)
SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM target_departments
);

Benefits of using CTEs:

• Each logical step is clearly defined and named
• No deeply nested parentheses
• Execution flow is easy to understand
• Logic can be reviewed and tested independently

By replacing deeply nested subqueries with CTEs, SQL becomes cleaner, flatter and self-explanatory. Each CTE acts as a readable building block, allowing developers to focus on what the query does rather than how to mentally untangle it. This is one of the most practical reasons CTEs are preferred in real-world, production-grade SQL.

3. Improve Query Readability

One of the biggest advantages of using Common Table Expressions (CTEs) is how significantly they improve query readability. A well-named CTE explains what the query is doing, not just how it is doing it. This turns SQL from a dense block of logic into a self-describing, easy-to-follow workflow.

Readable SQL benefits not only the original author, but also teammates, code reviewers and future maintainers who may encounter the query months or years later.

SELECT d.department_name, SUM(o.amount) AS total_sales
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN departments d ON c.department_id = d.department_id
WHERE o.order_date >= '2024-01-01'
AND o.status = 'COMPLETED'
GROUP BY d.department_name
HAVING SUM(o.amount) > 100000;

WITH completed_orders_2024 AS (
    SELECT customer_id, amount
    FROM orders
    WHERE order_date >= '2024-01-01'
      AND status = 'COMPLETED'
)
SELECT d.department_name, SUM(c.amount) AS total_sales
FROM completed_orders_2024 c
JOIN customers cu ON c.customer_id = cu.customer_id
JOIN departments d ON cu.department_id = d.department_id
GROUP BY d.department_name
HAVING SUM(c.amount) > 100000;

By using well-named CTEs, SQL becomes self-documenting, reducing reliance on comments and minimizing misunderstandings. This is why improving query readability is one of the most valuable and practical reasons to use CTEs in real-world database systems.

4. Enable Recursive and Hierarchical Queries

Common Table Expressions (CTEs) are the only practical and standardized way to write recursive queries in SQL. Recursive queries are essential when working with hierarchical or tree-structured data, where rows reference other rows within the same table.

Typical real-world scenarios include:

• Employee ->Manager relationships
• Category -> Subcategory hierarchies
• Organizational structures
• File and folder systems

Without CTEs, expressing these relationships requires complex, database-specific workarounds or multiple queries often making the logic hard to maintain or understand.

employee_id | employee_name | manager_id

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member (top-level employees)
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive member
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN employee_hierarchy h
        ON e.manager_id = h.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level, employee_name;

Without CTEs, hierarchical queries would be extremely difficult or outright impossible to write cleanly using standard SQL. This makes CTEs indispensable for any system that models relationships in a tree or hierarchy format.

5. Encourage Modular Query Design

Common Table Expressions (CTEs) promote a modular approach to SQL, very similar to how functions or methods are used in programming. Instead of writing one large, monolithic query, you can divide the logic into independent, well-defined components, where each CTE represents a single logical step in the data-processing flow.

Each CTE focuses on what it produces, not how it is consumed. These CTEs can then be combined, reused or extended within the same query, leading to cleaner and more flexible SQL.

SELECT d.department_name,
       SUM(o.amount) AS total_sales,
       COUNT(DISTINCT c.customer_id) AS customer_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN departments d ON c.department_id = d.department_id
WHERE o.status = 'COMPLETED'
  AND o.order_date >= '2024-01-01'
GROUP BY d.department_name
HAVING SUM(o.amount) > 50000;

WITH completed_orders AS (
    SELECT order_id, customer_id, amount
    FROM orders
    WHERE status = 'COMPLETED'
      AND order_date >= '2024-01-01'
),
sales_summary AS (
    SELECT customer_id,
           SUM(amount) AS total_sales
    FROM completed_orders
    GROUP BY customer_id
)
SELECT d.department_name,
       SUM(s.total_sales) AS department_sales,
       COUNT(DISTINCT s.customer_id) AS customer_count
FROM sales_summary s
JOIN customers c ON s.customer_id = c.customer_id
JOIN departments d ON c.department_id = d.department_id
GROUP BY d.department_name
HAVING SUM(s.total_sales) > 50000;

By encouraging modular query design, CTEs help SQL scale gracefully as business logic grows just like well-structured code in modern software development.

Storage and Execution Behavior of CTEs

A key point to understand is that a CTE is never stored permanently. It exists only for the duration of the query in which it is defined. How a CTE is executed depends on the database optimizer, which chooses the most efficient strategy based on the query structure, references and database engine.

Depending on the database engine and the specific query, a CTE can be handled in one of two Execution Strategies

This internal behavior has a direct impact on performance. A CTE that is materialized can be beneficial for reuse-heavy logic, while an inlined CTE allows the optimizer more flexibility for pushing filters and joins.

The exact behavior varies across database systems such as PostgreSQL, SQL Server, Oracle and MySQL and may also depend on query structure, database version and optimizer rules.

Understanding these execution behaviors helps in writing clean, efficient and predictable SQL, especially in production systems where query performance is critical.

1. Non-Recursive CTE

A non-recursive CTE is the most commonly used type of CTE. It defines a temporary result set that executes once and does not reference itself. Non-recursive CTEs are ideal for simplifying complex queries, replacing nested subqueries and improving readability and maintainability.

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

WITH high_salary_employees AS (
    SELECT name, department, salary
    FROM employees
    WHERE salary > 60000
)
SELECT *
FROM high_salary_employees;

WITH Sales_CTE AS (
    SELECT Region, SUM(Amount) AS TotalSales
    FROM Orders
    GROUP BY Region
),
Ranked_CTE AS (
    SELECT Region, TotalSales,
           RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
    FROM Sales_CTE
)
SELECT *
FROM Ranked_CTE
WHERE SalesRank <= 3;

2. Recursive CTE

A Recursive CTE is a query that references its own name. This allows SQL to perform iterative processing, which is essential for working with hierarchical or graph-based data.

The key characteristic of a recursive CTE is that it uses a UNION ALL to combine two parts: a starting point and a repeating logic block that references the CTE itself.

Recursive CTEs are ideal for queries involving hierarchical relationships, such as organizational charts (manager-employee relationships), bill of materials (product-component hierarchies) or traversing network paths.

This structure allows SQL to iteratively build a complete hierarchical result set in a clean and efficient manner.

WITH RECURSIVE CTE_Name (Column1, Column2, ...) AS (
    -- Anchor Member: the starting point of recursion
    SELECT Column1, Column2, ...
    FROM TableName
    WHERE Anchor_Condition

    UNION ALL

    -- Recursive Member: references the CTE itself
    SELECT t.Column1, t.Column2, ...
    FROM TableName t
    INNER JOIN CTE_Name c
        ON t.ParentColumn = c.Column1
    WHERE Recursive_Condition
)
SELECT *
FROM CTE_Name;

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: top-level employees
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: add subordinates
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN employee_hierarchy h
        ON e.manager_id = h.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level, employee_name;

Advantages of Using CTEs in SQL

WITH ActiveUsers AS (
    SELECT Id, Name
    FROM Users
    WHERE IsActive = 1
)
SELECT *
FROM ActiveUsers;

WITH OrderTotals AS (
    SELECT CustomerId, SUM(Amount) AS TotalAmount
    FROM Orders
    GROUP BY CustomerId
)
SELECT *
FROM OrderTotals
WHERE TotalAmount > 5000;

WITH RECURSIVE CategoryTree AS (
    SELECT Id, ParentId, Name
    FROM Categories
    WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.ParentId, c.Name
    FROM Categories c
    JOIN CategoryTree ct ON c.ParentId = ct.Id
)
SELECT * FROM CategoryTree;

WITH HighSalaryEmployees AS (
    SELECT Id, Name, Salary
    FROM Employees
    WHERE Salary > 80000
)
SELECT COUNT(*) FROM HighSalaryEmployees
UNION ALL
SELECT AVG(Salary) FROM HighSalaryEmployees;

WITH FilteredOrders AS (
    SELECT *
    FROM Orders
    WHERE OrderDate >= '2024-01-01'
)
SELECT *
FROM FilteredOrders
ORDER BY OrderDate DESC;

WITH RankedStudents AS (
    SELECT Name, Marks,
           RANK() OVER (ORDER BY Marks DESC) AS RankNo
    FROM Students
)
SELECT *
FROM RankedStudents
WHERE RankNo <= 3;

Disadvantages of Using CTEs in SQL