Common Table Expressions (CTE) in SQL

A CTE (Common Table Expression) is a temporary, named result set that is defined and exists only during the execution of a single query.

Imagine you’ve written a query with three nested subqueries inside the FROM, two in the WHERE, and a JOIN to another subquery.

If you try to read or modify that script a week later… you probably won’t understand absolutely anything about what it does.

To solve this chaos, SQL Server introduced CTEs. Their main purpose is not to improve performance (which is usually the same as a subquery), but to improve the readability and organization of our queries.

Basic Syntax: The WITH Clause

A CTE is defined before your main query using the WITH keyword.

WITH VentasTotales AS (
    -- Your complex query goes here
    SELECT ClienteID, SUM(Total) as TotalGastado
    FROM Pedidos
    GROUP BY ClienteID
)

-- Here begins the real query that uses the CTE
SELECT 
    C.Nombre,
    CTE.TotalGastado
FROM Clientes C

INNER JOIN VentasTotales CTE ON C.ClienteID = CTE.ClienteID

WHERE CTE.TotalGastado > 1000;

Chaining CTEs

The real magic emerges when you define multiple CTEs, where the second reads from the first, the third from the second, etc. This allows us to program in a “procedural” and logical way.

Imagine we want to:

With subqueries it would be a nightmare. With CTEs it’s a walk in the park:

WITH VentasPorEmpleado AS (
    -- Step 1: Raw grouped data
    SELECT EmpleadoID, SUM(Importe) as Total
    FROM Ventas
    GROUP BY EmpleadoID
),

PromedioGlobal AS (
    -- Step 2: Use step 1 to calculate the average
    SELECT AVG(Total) as MediaEmpresa
    FROM VentasPorEmpleado
)

-- Step 3: Cross both results
SELECT 
    V.EmpleadoID, 
    V.Total,
    P.MediaEmpresa
FROM VentasPorEmpleado V
CROSS JOIN PromedioGlobal P
WHERE V.Total > P.MediaEmpresa;

Recursive CTEs

So far, CTEs are “syntactic sugar” for organizing code. But there’s something normal subqueries cannot do and CTEs can: call themselves.

Structure of a Recursion

It consists of two parts joined by a UNION ALL:

Let’s see it with an example of a company organizational chart.

We have an Empleados table with ID and JefeID. We want to list all employees and know at what level of the hierarchy they are.

It would look something like this:

WITH Organigrama AS (
    -- 1. ANCHOR: Find the CEO (The one with no boss or JefeID is NULL)
    SELECT EmpleadoID, Nombre, JefeID, 1 AS Nivel
    FROM Empleados
    WHERE JefeID IS NULL
    
    UNION ALL
    
    -- 2. RECURSIVE: Find employees whose Boss is in the previous step
    SELECT 
        E.EmpleadoID, 
        E.Nombre, 
        E.JefeID, 
        O.Nivel + 1 -- Increase the level
    FROM Empleados E
    INNER JOIN Organigrama O ON E.JefeID = O.EmpleadoID
)
-- Use the final result
SELECT * FROM Organigrama ORDER BY Nivel;