Diving Into SQL's Hidden Gems: Common Table Expressions (CTEs)

In the vast world of SQL, the WITH clause, or Common Table Expressions (CTEs), stands out as an underused treasure. It gives a complex query a structure you can read from top to bottom, one named step at a time, instead of a pile of nested subqueries you have to read from the inside out.

The examples below are T-SQL, for SQL Server, but CTEs are standard SQL: PostgreSQL, MySQL 8, SQLite, Oracle and the rest support the same basic syntax, with differences in recursion and data modification noted along the way.

What is a CTE?

A CTE is a named result set that exists only for the statement that follows it. You define it with WITH name AS (query), and the statement right after can use the name as if it were a table:

WITH SalesSummary AS (
    SELECT
        ProductID,
        SUM(Sales) AS TotalSales
    FROM Sales
    GROUP BY ProductID
)
SELECT *
FROM SalesSummary
WHERE TotalSales > 1000;

The CTE summarises sales by product, and the outer query keeps the products above 1,000. The same query written with a derived table works too, but the filter ends up reading before the thing it filters:

SELECT *
FROM (
    SELECT ProductID, SUM(Sales) AS TotalSales
    FROM Sales
    GROUP BY ProductID
) AS SalesSummary
WHERE TotalSales > 1000;

Three rules are worth knowing from the start:

• A CTE lives for one statement. The SELECT, INSERT, UPDATE, DELETE or MERGE right after the WITH can use it; the next statement cannot.
• Every column needs a name. In SQL Server, SUM(Sales) without an alias is an error inside a CTE (PostgreSQL just names the column sum). You can also name the columns in a list: WITH SalesSummary (ProductID, TotalSales) AS (...).
• In T-SQL, the statement before a WITH must end with a semicolon. WITH has other meanings in T-SQL (WITH XMLNAMESPACES and WITH CHANGE_TRACKING_CONTEXT, for two), so a missing semicolon on the line before gives a syntax error. That’s why you often see ;WITH in SQL Server code.

Chaining CTEs

The real value shows when a query has several steps. You can define more than one CTE in the same WITH, separated by commas, and each one can use the ones defined before it.

Say we want the three best-selling products of each category. Step one totals the sales; step two ranks the products inside their category; the final query keeps the top three:

WITH SalesSummary AS (
    SELECT p.CategoryID, s.ProductID, SUM(s.Sales) AS TotalSales
    FROM Sales AS s
    JOIN Products AS p ON p.ProductID = s.ProductID
    GROUP BY p.CategoryID, s.ProductID
),
Ranked AS (
    SELECT
        CategoryID,
        ProductID,
        TotalSales,
        ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY TotalSales DESC) AS Position
    FROM SalesSummary
)
SELECT CategoryID, ProductID, TotalSales
FROM Ranked
WHERE Position <= 3
ORDER BY CategoryID, Position;

Each step has a name that says what it holds, and each can be tested on its own: replace the final SELECT with SELECT * FROM SalesSummary and you see the intermediate result. That habit alone makes long reporting queries much easier to debug.

The ranking step also shows why CTEs and window functions go together. You can’t filter on ROW_NUMBER() in the same SELECT that computes it (WHERE runs before the window function), so you compute it in one step and filter it in the next.

What CTEs do for performance (and what they don’t)

A common belief is that a CTE is computed once and stored, like a temporary table. In SQL Server it isn’t. A non-recursive CTE is expanded into the query that uses it, exactly like a view or a derived table, and the optimiser plans the whole thing as one query. Two consequences follow:

• A CTE is not slower than the equivalent subquery. It produces the same plan. Rewriting nested subqueries as CTEs is a readability change, not a performance risk.
• A CTE referenced twice is normally evaluated twice. If you join a CTE to itself, or use it in both halves of a UNION, SQL Server reads the underlying tables for each reference. For an expensive step you reuse, a temporary table (SELECT ... INTO #SalesSummary) computes it once and lets you index it.

PostgreSQL behaved differently until version 12: every CTE was an “optimisation fence”, computed once and materialised. Since version 12, PostgreSQL inlines a CTE that is not recursive, has no side effects and is referenced only once, and materialises the others. You can choose with WITH SalesSummary AS MATERIALIZED (...) or AS NOT MATERIALIZED (...). So when someone says “CTEs are slow” or “CTEs are cached”, the answer depends on the database and its version.

Recursive CTEs

A CTE can refer to itself, which lets SQL walk a hierarchy of unknown depth: an organisation chart, a category tree, a bill of materials. A recursive CTE has two parts joined by UNION ALL: the anchor, which returns the starting rows, and the recursive member, which joins the CTE to the table to find the next level. SQL Server repeats the recursive member until it returns no rows.

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    ManagerID int NULL REFERENCES Employees (EmployeeID)
);

INSERT INTO Employees VALUES
    (1, N'Ana', NULL),
    (2, N'Bruno', 1),
    (3, N'Carla', 1),
    (4, N'Diego', 2),
    (5, N'Eva', 4);

WITH OrgChart AS (
    -- Anchor: whoever has no manager.
    SELECT EmployeeID, Name, ManagerID, 0 AS Level,
           CAST(Name AS nvarchar(400)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: the direct reports of the previous level.
    SELECT e.EmployeeID, e.Name, e.ManagerID, o.Level + 1,
           CAST(o.Path + N' > ' + e.Name AS nvarchar(400))
    FROM Employees AS e
    JOIN OrgChart AS o ON e.ManagerID = o.EmployeeID
)
SELECT Level, Path
FROM OrgChart
ORDER BY Path;

The result reads like the chart itself:

Two details matter here. The anchor and the recursive member must return the same column types, which is why Path is cast to nvarchar(400) in both; without the casts, SQL Server stops with “Types don’t match between the anchor and the recursive part”. And SQL Server stops a recursion after 100 levels by default, with an error. If your data is deeper, raise the limit for that statement with OPTION (MAXRECURSION 1000) after the final SELECT, or remove it with MAXRECURSION 0, which is only safe when the data can’t contain a cycle. Starting from the root, as here, a cycle is never reached; but anchor on a specific employee (WHERE EmployeeID = @id) and let someone in that chain be, through some chain, their own manager, and the recursion never ends: it fails at the MAXRECURSION limit, or runs forever with MAXRECURSION 0.

Recursion is also handy for generating rows. This builds every date of 2026, for example to fill the gaps in a sales report:

WITH Calendar AS (
    SELECT CAST('2026-01-01' AS date) AS Day
    UNION ALL
    SELECT DATEADD(day, 1, Day)
    FROM Calendar
    WHERE Day < '2026-12-31'
)
SELECT Day
FROM Calendar
OPTION (MAXRECURSION 366);

Without the OPTION, the query fails after 100 recursion levels (101 days, up to 11 April), and you get a partial result followed by the error.

On SQL Server 2022 and later, SELECT DATEADD(day, value, '2026-01-01') AS Day FROM GENERATE_SERIES(0, 364) does the same without recursion, and faster; PostgreSQL has generate_series.

PostgreSQL and MySQL need a keyword for a recursive CTE: WITH RECURSIVE. SQLite accepts it but doesn’t require it. SQL Server and Oracle don’t accept it, and Oracle also requires the column list (WITH OrgChart (EmployeeID, Name, ...) AS (...)).

Changing data through a CTE

In SQL Server a CTE that reads from a single table can be the target of an UPDATE or a DELETE, and the change goes to that table. The classic use is removing duplicates while keeping one row of each:

WITH Numbered AS (
    SELECT
        CustomerID, Email, CreatedAt,
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CreatedAt, CustomerID) AS Copy
    FROM Customers
    WHERE Email IS NOT NULL
)
DELETE FROM Numbered
WHERE Copy > 1;

Each group of rows with the same email is numbered by creation date (then by id, so ties are decided the same way every time), and every row after the first is deleted. Without the WHERE, all customers with no email would fall into one group and be deleted as “duplicates”. “The same email” follows the column’s collation: with the usual case-insensitive one, A@x.com and a@x.com are the same group, so normalise first if that’s not what you want. Before running it for real, replace DELETE FROM Numbered with SELECT * FROM Numbered to see the rows that would go, and run it inside a transaction you can roll back.

Best practices

• Name each step after what it holds: SalesSummary, Ranked, ActiveCustomers, not cte1 and cte2.
• One idea per CTE. If a CTE needs a comment to explain three different things, it’s probably three CTEs.
• Don’t count on a CTE to cache anything. If a result is expensive and used more than once, put it in a temporary table and look at the execution plan.
• Guard recursive CTEs: a MAXRECURSION that fits your data, and a level counter you can filter on when the data might contain cycles.
• Remember the semicolon before WITH in T-SQL scripts and stored procedures.

Conclusion

CTEs don’t make SQL do anything it couldn’t do before, apart from recursion. What they change is how a query reads: as a sequence of named steps that you can inspect one at a time, which matters most in the long reporting and data-cleaning queries that people come back to months later. Use them for structure and for hierarchies, check the plan when performance matters, and reach for a temporary table when a step is expensive enough to compute only once.