CTE en SQL: Una guía completa con ejemplos

Cómo crear una CTE SQL

Al crear un CTE, utilizamos la palabra clave WITH para iniciar la definición del CTE. La sintaxis general de un CTE es la siguiente:

WITH cte_name (column1, column2, ...)
AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Dónde:

• WITH: Inicia la definición del CTE, indicando que el nombre siguiente representa un conjunto de resultados temporal.

• cte_name: El nombre se asigna al CTE para referenciarlo en la consulta principal.

• Lista de columnas opcional (column1, column2, ...): Especifica los nombres de las columnas del conjunto de resultados de la CTE. Esto es útil cuando hay que ajustar los nombres de las columnas.

• Consulta que define el CTE: La consulta interna que selecciona los datos y da forma al conjunto temporal de resultados.

• Consulta principal: Hace referencia al CTE por su nombre, utilizándolo como una tabla.

Veamos el siguiente ejemplo de creación de un CTE utilizando un enfoque por capas. Supongamos que tenemos una tabla Employees, y queremos crear un CTE que seleccione a los empleados que ganan un salario superior a 50.000$.

Paso 1: Escribe la consulta base

Empezaremos escribiendo la consulta básica SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Paso 2: Envuelve la consulta utilizando la palabra clave WITH para crear una CTE

Utiliza la palabra clave WITH para dar un nombre al CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Paso 3: Utiliza el CTE en la consulta principal

Por último, haz referencia al CTE en una sentencia SELECT llamando al nombre del CTE definido anteriormente.

-- Define a Common Table Expression (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Use the CTE to select high-earning employees
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Para resumir los pasos anteriores, hemos utilizado la palabra clave WITH para definir el CTE llamado HighEarningEmployees. La consulta interna se utilizó para generar el conjunto de datos temporal. La consulta principal hace referencia a HighEarningEmployees para mostrar las columnas especificadas EmployeeID, FirstName y LastName.

Por qué son útiles las CTE de SQL

A partir del ejemplo anterior, puede que te preguntes por qué utilizamos CTEs cuando incluso las consultas simples dan los mismos resultados. Las razones son las siguientes:

Simplifica las consultas complejas

Las CTE descomponen las sentencias SQL complejas en partes más pequeñas y manejables, haciendo que el código sea más fácil de leer, escribir y mantener. 

Supongamos que tenemos tres tablas Orders, Customers, y Products. Queremos hallar los ingresos totales generados por cada cliente que compró en 2024. Cuando escribimos la consulta sin utilizar CTE, parece desordenada y difícil de leer y entender.

-- Select customer names and total revenue from their orders
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Join to get customer and products table
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Utilizando una CTE, podemos separar la lógica en un formato más legible:

-- Define the CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--Main query
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Reutilización del código

Las CTE ayudan a evitar la duplicación, ya que permiten reutilizar el mismo conjunto de resultados en distintas partes de una consulta. Si varios cálculos u operaciones se basan en el mismo conjunto de datos, puedes definirlo una vez en una CTE y referirte a él cuando sea necesario.

Supongamos que necesitamos calcular las ventas medias y totales de cada categoría de productos en una base de datos de comercio electrónico. Podemos utilizar una CTE para definir los cálculos una vez y reutilizarlos en consultas posteriores.

-- Define a CTE to calculate total and average sales for each category
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select category, total sales, and average sales from the CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Otras aplicaciones

Además de simplificar las consultas y la reutilización del código, las CTE también tienen otros usos. No puedo abarcar en detalle todos los usos posibles de los CTE. Nuestro curso Manipulación de Datos en SQL es una gran opción si quieres seguir practicando. Sin embargo, documentaré aquí algunas de las otras razones principales:

• Organización y legibilidad de las consultas: Las CTE mejoran la legibilidad del código SQL dividiendo las consultas en pasos lógicos y secuenciales. Cada paso del proceso de consulta puede representarse con su propia CTE, lo que facilita el seguimiento de toda la consulta.
• Recorrido jerárquico de datos: Los CTE pueden ayudar a navegar por relaciones jerárquicas, como estructuras organizativas, relaciones padre-hijo o cualquier modelo de datos que implique niveles anidados. Las CTE recursivas son útiles para consultar datos jerárquicos porque te permiten recorrer niveles de forma iterativa.
• Agregaciones multinivel: Los CTEs pueden ayudar a realizar agregaciones a varios niveles, como calcular las cifras de ventas a diferentes granularidades (por ejemplo, por mes, trimestre y año). Utilizar CTEs para separar estos pasos de agregación garantiza que cada nivel se calcule de forma independiente y lógica.
• Combinar datos de varias tablas: Se pueden utilizar múltiples CTEs para combinar datos de diferentes tablas, haciendo que el paso final de combinación sea más estructurado. Este enfoque simplifica las uniones complejas y garantiza que los datos de origen se organicen de forma lógica para mejorar la legibilidad.

Técnicas avanzadas de SQL CTE

Las CTE admiten técnicas SQL avanzadas, lo que las hace versátiles y útiles para distintos casos de uso. Éstas son algunas de las aplicaciones avanzadas de los CTE.

Múltiples CTEs en una sola consulta

Puedes definir varios CTE en una sola consulta, lo que permite realizar transformaciones y cálculos complejos. Este método es útil cuando un problema requiere múltiples etapas de procesamiento de datos, donde cada CTE representa una etapa distinta.

Supongamos que tenemos datos de ventas en una tabla llamada Sales y queremos calcular las ventas totales de cada producto, identificar los productos con ventas totales superiores a la media y clasificar estos productos en función de sus ventas totales.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Step 2: Calculate the average total sales across all products
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Step 3: Filter products with above-average total sales
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Step 4: Rank the high-sales products
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

En el ejemplo anterior;

• El primer CTE (ProductSales) calcula las ventas totales por producto.

• El segundo CTE (AverageSales) calcula la media de las ventas totales de todos los productos.

• El tercer CTE (HighSalesProducts) filtra los productos cuyas ventas totales superan la media.

• La consulta final clasifica estos productos en función de sus ventas totales.

CTEs en sentencias UPDATE, DELETE y MERGE

Cuando se incorporan a las operaciones UPDATE, DELETE y MERGE, las CTE pueden simplificar las tareas de manipulación de datos, especialmente cuando se trata de filtros complejos o datos jerárquicos.

Utilizar CTE con una sentencia UPDATE

Supongamos que tenemos una tabla Employees con una columna EmployeeSalary. Queremos dar un aumento del 10% a todos los empleados que lleven más de 5 años trabajando para la empresa.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

El CTE LongTermEmployees identifica a los empleados que han trabajado más de cinco años. La declaración UPDATE utiliza este CTE para aumentar selectivamente los salarios.

Utilizar CTE con una sentencia DELETE

Ahora supongamos que tenemos una tabla llamada Products y queremos eliminar todos los productos que no se han vendido en los últimos 2 años. Podemos utilizar un CTE para filtrar los productos:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Use DATEADD to find products with a LastSoldDate more than 2 years ago
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

El CTE OldProducts identifica los productos que no se han vendido en los últimos dos años, y luego la declaración DELETE utiliza este CTE para eliminar esos productos.

Utilizar CTE con una sentencia MERGE

La sentencia MERGE de SQL permite realizar actualizaciones, inserciones o eliminaciones condicionales en una tabla de destino basándose en los datos de una tabla de origen. En el siguiente ejemplo, el CTE MergedInventory combina datos de inventario nuevos y existentes. A continuación, la declaración MERGE actualiza las cantidades de los productos existentes o inserta nuevos productos basándose en los datos del CTE.

-- CTE to merge new and existing inventory data
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Use LEFT JOIN to include all new data, even if not in current inventory
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Expresiones de tabla comunes (CTE) recursivas

Las CTE recursivas ayudan a realizar operaciones avanzadas y repetidas.

Introducción a los CTE recursivos

Las CTE recursivas son un tipo especial de CTE que se referencia a sí misma dentro de su definición, permitiendo que la consulta realice operaciones repetidas. Esto los hace ideales para trabajar con datos jerárquicos o estructurados en forma de árbol, como organigramas, estructuras de directorios o conjuntos de productos. El CTE recursivo procesa los datos de forma iterativa, devolviendo los resultados paso a paso hasta que se cumple una condición de finalización.

Miembros ancla y recursivos

Un CTE recursivo consta de dos partes principales:

• Miembro Ancla: La parte que define la consulta base que inicia la recursión.
• Miembro recursivo: La parte que hace referencia al propio CTE, permitiéndole realizar las operaciones "recursivas".

Supongamos que tenemos una tabla Employees, en la que cada fila contiene un EmployeeID, EmployeeName, y ManagerID. Si queremos encontrar todos los informes directos e indirectos de un directivo concreto, empezamos por el miembro de anclaje que identifica al directivo de nivel superior. El miembro ancla comienza con el empleado con EmployeeID = 1.

El miembro recursivo busca empleados cuya ManagerID coincida con la EmployeeID de la iteración anterior. Cada iteración recupera el siguiente nivel de la jerarquía.

WITH EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    UNION ALL
    -- Recursive member: find employees who report to the current managers
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Posibles problemas o limitaciones de las CTE en SQL

Comprender las características y limitaciones de las CTE es importante para escribir consultas lógicas y legibles. Veamos algunas limitaciones y posibles problemas de la utilización de CTEs en diferentes bases de datos.

Limitaciones de SQL Server y Azure

Existen algunas limitaciones específicas del entorno para los CTE de SQL cuando se trabaja con SQL Server o Azure Synapse Analytics. Incluyen lo siguiente:

• Servidor SQL: El nivel máximo de recursividad por defecto para los CTE recursivos es 100, que puede modificarse utilizando la sugerencia OPTION (MAXRECURSION). Si se supera este límite sin ajuste, se produce un error. Los CTEs no pueden anidarse directamente unos dentro de otros ni definirse dentro de otro CTE.

• Azure Synapse Analytics: Los CTEs tienen un soporte limitado para ciertas operaciones SQL como INSERT, UPDATE, DELETE, y MERGE. Además, los CTE recursivos no son compatibles en los entornos basados en la nube de Azure Synapse Analytics, lo que restringe la capacidad de realizar determinadas operaciones de datos jerárquicos.

Si te encuentras trabajando con SQL Server, debes saber que DataCamp tiene un montón de recursos estupendos para ayudarte. Para empezar, te recomiendo que sigas el curso Introducción a SQL Server de DataCamp para dominar los fundamentos de SQL Server para el análisis de datos. Puedes probar nuestro itinerario profesional de Desarrollador de SQL Server, que abarca desde las transacciones y la gestión de errores hasta el análisis de series temporales. Nuestro curso Consultas jerárquicas y recursivas en SQL Server llega directamente al meollo de cómo escribir consultas avanzadas en SQL Server, incluidos los métodos que implican CTE.

Otros posibles problemas

Aunque las CTE son útiles para simplificar consultas complejas, hay algunos errores comunes que debes conocer. Incluyen lo siguiente:

• Bucles infinitos en CTEs recursivos: Si no se cumple la condición de finalización de una CTE recursiva, puede producirse un bucle infinito, haciendo que la consulta se ejecute indefinidamente. Para evitar que la ETC recursiva se ejecute infinitamente, utiliza la sugerencia OPTION (MAXRECURSION N) para limitar el número máximo de iteraciones recursivas, donde N es un límite especificado.

• Consideraciones sobre el rendimiento: Los CTEs recursivos pueden consumir muchos recursos si la profundidad de la recursión es alta o se procesan grandes conjuntos de datos. Para optimizar el rendimiento, limita los datos procesados en cada iteración y garantiza un filtrado adecuado para evitar niveles de recursión excesivos.

Cuándo utilizar CTEs frente a Otras técnicas

Aunque las CTE son adecuadas para simplificar las consultas que implican tareas repetidas, las tablas derivadas, las vistas y las tablas temporales también sirven para fines similares. La tabla siguiente destaca las ventajas e inconvenientes de cada método y cuándo utilizar cada uno.

Conclusión

Dominar los CTE requiere práctica, como todo: Te recomiendo que pruebes la carrera de Analista de Datos Asociado en SQL de DataCamp para convertirte en un analista de datos competente. El curso de Informes en SQL también te ayudará a dominar la creación de informes y cuadros de mando complejos para una presentación eficaz de los datos. Por último, deberías obtener la Certificación de Asociado SQL para demostrar tu dominio en el uso de SQL para resolver problemas empresariales y destacar entre otros profesionales.