CTE en SQL: Una Guía Completa con Ejemplos

Cómo crear un CTE en 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 (
    -- Consulta que define el CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Consulta principal
SELECT ...
FROM cte_name;

Donde:

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

• cte_name: El nombre se asigna al CTE para hacer referencia a él en la consulta principal.

• Lista de columnas opcional (columna1, columna2, …): Especifica los nombres de las columnas para el conjunto de resultados del CTE. Esto es útil cuando se necesitan ajustar los nombres de las columnas.

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

• Consulta principal: Hace referencia al CTE por su nombre, usá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: Escribir la consulta base

Comenzamos escribiendo la consulta básica SELECT:

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

Paso 2: Envolver la consulta usando la palabra clave WITH para crear un CTE

Usa la palabra clave WITH para darle un nombre al CTE.

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

Paso 3: Usar el CTE en la consulta principal

Finalmente, se hace referencia al CTE en una declaración SELECT llamando al nombre del CTE definido anteriormente.

-- Definir una Expresión de Tabla Común (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Usar el CTE para seleccionar empleados de altos ingresos
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é los CTE de SQL son útiles

Del ejemplo anterior, es posible que te preguntes por qué usamos CTEs cuando incluso consultas simples arrojan los mismos resultados. Las siguientes son las razones:

Simplificar consultas complejas

Los CTEs dividen declaraciones SQL complejas en partes más pequeñas y manejables, lo que hace que el código sea más fácil de leer, escribir y mantener. 

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

-- Seleccionar nombres de clientes e ingreso total de sus órdenes
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Unir para obtener tablas de clientes y productos
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;

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

-- Definir el 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
)
--Consulta principal
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Reutilización de código

Los CTEs ayudan a evitar la duplicación al permitir que el mismo conjunto de resultados se reutilice en diferentes partes de una consulta. Si múltiples cálculos u operaciones se basan en el mismo conjunto de datos, puedes definirlo una vez en un CTE y hacer referencia a él según sea necesario.

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

-- Definir un CTE para calcular las ventas totales y el promedio para cada categoría
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Seleccionar categoría, ventas totales y ventas promedio del CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Otras aplicaciones

Además de simplificar consultas y reutilizar código, los CTE tienen otros usos también.No puedo cubrir cada posible uso de CTEs en detalle. Nuestro curso Manipulación de Datos en SQL es una excelente opción si deseas seguir practicando. Sin embargo, documentaré algunas de las principales razones aquí:

• Organización y Legibilidad de Consultas: Las CTE mejoran la legibilidad del código SQL al dividir las consultas en pasos lógicos y secuenciales. Cada paso en el proceso de consulta puede ser representado por su propia CTE, lo que hace que toda la consulta sea más fácil de seguir.
• Travesía de Datos Jerárquicos: Las CTE pueden ayudar a navegar relaciones jerárquicas, como estructuras organizativas, relaciones padre-hijo o cualquier modelo de datos que involucre niveles anidados. Las CTE recursivas son útiles para consultar datos jerárquicos porque te permiten recorrer niveles de forma iterativa.
• Agregaciones de Múltiples Niveles: Los CTEs pueden ayudar a realizar agregaciones en múltiples niveles, como calcular cifras de ventas en diferentes granularidades (por ejemplo, por mes, trimestre y año). Usar CTEs para separar estos pasos de agregación asegura que cada nivel se calcule de manera independiente y lógica.
• Combinando Datos de Múltiples Tablas: Múltiples CTEs pueden ser utilizados para combinar datos de diferentes tablas, haciendo que el paso final de combinación sea más estructurado. Este enfoque simplifica uniones complejas y asegura que los datos de origen estén organizados lógicamente para mejorar la legibilidad.

Técnicas Avanzadas de CTE en SQL

Los CTEs soportan técnicas avanzadas de SQL, haciéndolos versátiles y útiles para diferentes casos de uso. A continuación se presentan algunas de las aplicaciones avanzadas de los CTEs.

Múltiples CTEs en una sola consulta

Puedes definir múltiples CTEs en una sola consulta, lo que permite 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 Ventas y queremos calcular las ventas totales para cada producto, identificar productos con ventas totales por encima del promedio, y clasificar estos productos según sus ventas totales.

WITH ProductSales AS (
    -- Paso 1: Calcular las ventas totales para cada producto
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Paso 2: Calcular el promedio de ventas totales entre todos los productos
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Paso 3: Filtrar productos con ventas totales por encima del promedio
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Paso 4: Clasificar los productos de alta venta
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

En el ejemplo anterior;

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

• El segundo CTE (AverageSales) calcula el promedio 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 en operaciones UPDATE, DELETE y MERGE, los CTE pueden simplificar las tareas de manipulación de datos, especialmente al tratar con filtros complejos o datos jerárquicos.

Uso de CTE con una sentencia UPDATE

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

-- Definir un CTE para encontrar empleados contratados hace más de 5 años
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Actualizar salarios en un 10% para empleados a largo plazo identificados en el 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 sentencia UPDATE utiliza este CTE para aumentar selectivamente los salarios.

Usando CTE con una declaración DELETE

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

-- Definir un CTE para identificar productos no vendidos en los últimos 2 años
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Usar DATEADD para encontrar productos con una fecha de última venta hace más de 2 años
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Eliminar productos identificados como antiguos de la tabla principal
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

El CTE ProductosAntiguos 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.

Usando CTE con una declaración MERGE

La declaración MERGE en SQL permite actualizaciones condicionales, inserciones o eliminaciones en una tabla de destino en función de los datos en una tabla de origen. En el siguiente ejemplo, el CTE InventarioCombinado combina datos de inventario nuevos y existentes. La declaración MERGE luego actualiza las cantidades para productos existentes o inserta nuevos productos según los datos del CTE.

-- CTE para fusionar datos de inventario nuevos y existentes
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Usar LEFT JOIN para incluir todos los datos nuevos, incluso si no están en el inventario actual
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Fusionar los datos preparados en la tabla de Inventario
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Actualizar productos existentes con nuevas cantidades
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Insertar nuevos productos si no existen en el inventario
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Expresiones de Tabla Comunes Recursivas (CTEs)

Las CTEs recursivas ayudan a realizar operaciones avanzadas y repetidas.

Introducción a las CTEs recursivas

Las CTEs recursivas son un tipo especial de CTE que se referencia a sí misma dentro de su definición, lo que permite que la consulta realice operaciones repetidas. Esto las hace ideales para trabajar con datos jerárquicos o estructurados en árbol, como organigramas, estructuras de directorio o ensamblajes de productos. La CTE recursiva procesa los datos de manera iterativa, devolviendo resultados paso a paso hasta que se cumple una condición de terminación.

Miembros ancla y recursivos

Una CTE recursiva 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 a la CTE en sí misma, permitiéndole realizar operaciones “recursivas”.

Supongamos que tenemos una tabla de Empleados, donde cada fila contiene un EmployeeID, EmployeeName y ManagerID. Si queremos encontrar todos los reportes directos e indirectos para un gerente específico, comenzamos con el miembro ancla que identifica al gerente de nivel superior. El miembro ancla comienza con el empleado con EmployeeID = 1.

El miembro recursivo encuentra empleados cuyo ManagerID coincide con el EmployeeID de la iteración anterior. Cada iteración recupera el próximo nivel de la jerarquía.

WITH EmployeeHierarchy AS (
    -- Miembro ancla: selecciona al gerente de nivel superior
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Comenzando con el gerente de nivel superior
    UNION ALL
    -- Miembro recursivo: encuentra empleados que reportan a los gerentes actuales
    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 CTEs en SQL

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

Limitaciones de SQL Server y Azure

Existen limitaciones específicas del entorno para CTEs de SQL al trabajar con SQL Server o Azure Synapse Analytics. Estas incluyen lo siguiente:

• SQL Server: El nivel máximo de recursión predeterminado para CTEs recursivos es 100, lo cual puede modificarse usando la sugerencia OPTION (MAXRECURSION). Si se supera este límite sin ajuste, se produce un error. Los CTEs no pueden anidarse directamente entre sí ni definirse dentro de otro CTE.

• Azure Synapse Analytics: Los CTE tienen 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 ciertas operaciones de datos jerárquicos.

Si te encuentras trabajando con SQL Server, debes saber que DataCamp tiene muchos recursos excelentes para ayudarte. Para comenzar, te recomiendo tomar el curso Introducción a SQL Server de DataCamp para dominar los conceptos básicos de SQL Server para análisis de datos. Puedes probar nuestra trayectoria profesional SQL Server Developer, que abarca desde transacciones y manejo de errores hasta análisis de series temporales. Nuestro curso Consultas Jerárquicas y Recursivas en SQL Server va directo al corazón de cómo escribir consultas avanzadas en SQL Server, incluidos los métodos que involucran CTE.

Otros problemas potenciales

Aunque las CTE son útiles para simplificar consultas complejas, hay algunos errores comunes de los que debes ser consciente. Estos incluyen lo siguiente:

• Bucles infinitos en CTE recursivas: Si la condición de terminación de una CTE recursiva no se cumple, puede resultar en un bucle infinito, haciendo que la consulta se ejecute indefinidamente. Para evitar que la CTE 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 de rendimiento: Los CTE recursivos pueden volverse intensivos en recursos si la profundidad de la recursión es alta o si se están procesando conjuntos de datos grandes. Para optimizar el rendimiento, limite los datos procesados en cada iteración y asegúrese de aplicar un filtrado adecuado para evitar niveles excesivos de recursión.

Cuándo usar CTE frente a otras técnicas

Si bien los CTE son apropiados para simplificar consultas que involucran tareas repetidas, las tablas derivadas, las vistas y las tablas temporales también cumplen propósitos similares. La siguiente tabla destaca las ventajas y desventajas de cada método y cuándo usar cada uno.

Conclusión

Dominar los CTEs requiere práctica, como cualquier otra cosa: recomiendo probar la trayectoria profesional Associate Data Analyst in SQL de DataCamp para convertirse en un analista de datos competente. El curso Reporting in SQL también le ayudará a ser competente en la creación de informes complejos y paneles de control para una presentación efectiva de datos. Finalmente, debería obtener la certificación de Asociado en SQL para demostrar su dominio en el uso de SQL para resolver problemas empresariales y destacar entre otros profesionales.