Qué significa expresión de tabla común o CTE en SQL Server y para qué sirve

Resumen: en este tutorial, aprenderás sobre la expresión de tabla común o CTE en SQL Server mediante el uso de la cláusula WITH.

Introducción a CTE en SQL Server

CTE significa expresión de tabla común. Una CTE permite definir un conjunto de resultados con nombre temporal que está disponible temporalmente en el ámbito de ejecución de una declaración como SELECT, INSERT, UPDATE, DELETE, o MERGE.

A continuación, se muestra la sintaxis común de un CTE en SQL Server:

WITH expression_name[(column_name [,...])]
AS
    (CTE_definition)
SQL_statement;

En esta sintaxis:

• Primero, se especifica el nombre de la expresión (expression_name) al que se puede hacer referencia más adelante en una consulta.
• A continuación, se especifica una lista de columnas separadas por comas después de expression_name. El número de columnas debe ser el mismo que el número de columnas definido en CTE_definition.
• Luego, se use la palabra clave AS después del nombre de la expresión o la lista de columnas si se especifica la lista de columnas.
• Después, se define una instrucción SELECT cuyo conjunto de resultados completa la expresión de la tabla común.
• Finalmente, se consulta la expresión de tabla común en una consulta (instruccion_SQL) como un SELECT, INSERT, UPDATE, DELETE, o MERGE.

Preferimos usar las expresiones de tabla comunes en lugar de usar subconsultas porque las expresiones de tabla comunes son más legibles. También usamos CTE en las consultas que contienen funciones analíticas

Ejemplos de CTE en SQL Server

Tomemos algunos ejemplos del uso de expresiones de tabla comunes.

A) Ejemplo simple de CTE de SQL Server

Esta consulta utiliza un CTE para devolver los montos de ventas por parte del personal de ventas en 2018 de la base de datos de pruebas:

WITH cte_sales_amounts (staff, sales, year) AS (
    SELECT    
        first_name + ' ' + last_name,
        SUM(quantity * list_price * (1 - discount)),
        YEAR(order_date)
    FROM    
        sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
    INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
    GROUP BY
        first_name + ' ' + last_name,
        year(order_date)
)

SELECT
    staff,
    sales
FROM
    cte_sales_amounts
WHERE
    year = 2018;

Resultado:

En este ejemplo:

• Primero, definimos cte_sales_amounts como el nombre de la expresión de tabla común. el CTE devuelve un resultado que consta de tres columnas staff, year y sales derivadas de la consulta de definición.
• En segundo lugar, construimos una consulta que devuelve el monto total de ventas por personal de ventas y año consultando los datos de las tablas orders, order_items y staffs.
• Tercero, nos referimos al CTE en la consulta externa y seleccionamos solo las filas cuyo año sea 2018.

Ten en cuenta que este ejemplo es únicamente para fines de demostración para ayudar a comprender gradualmente cómo funcionan las expresiones de tabla comunes. Hay una forma más óptima de lograr el resultado sin usar CTE.

B) Usar una expresión de tabla común para hacer informes de promedios basados en conteos

Este ejemplo usa el CTE para devolver la cantidad promedio de pedidos de ventas en 2018 para todo el personal de ventas.

WITH cte_sales AS (
    SELECT
        staff_id,
        COUNT(*) order_count  
    FROM
        sales.orders
    WHERE
        YEAR(order_date) = 2018
    GROUP BY
        staff_id

)
SELECT
    AVG(order_count) average_orders_by_staff
FROM
    cte_sales;

Resultado:

average_orders_by_staff
-----------------------
48

(1 row affected)

En este ejemplo:

Primero, usamos cte_sales como el nombre de la expresión de tabla común. Omitimos la lista de columnas del CTE para que se derive de la declaración de definición de CTE. En este ejemplo, incluye las columnas staff_id y order_count.

En segundo lugar, usamos la siguiente consulta para definir el conjunto de resultados que completa la expresión de tabla común cte_sales. La consulta devuelve el número de pedidos en 2018 por parte del personal de ventas.

SELECT    
    staff_id,
    COUNT(*) order_count
FROM    
    sales.orders
WHERE
    YEAR(order_date) = 2018
GROUP BY
    staff_id;

En tercer lugar, nos referimos a cte_sales en la declaración externa y usamos la función AVG() para obtener el pedido de venta promedio de todo el personal.

SELECT
    AVG(order_count) average_orders_by_staff
FROM
    cte_sales;

C) Usar múltiples CTE de SQL Server en un solo ejemplo de consulta

El siguiente ejemplo utiliza dos CTE cte_category_counts y cte_category_sales para devolver el número de productos y ventas para cada categoría de producto. La consulta externa une dos CTE mediante la columna category_id

WITH cte_category_counts (
    category_id,
    category_name,
    product_count
)
AS (
    SELECT
        c.category_id,
        c.category_name,
        COUNT(p.product_id)
    FROM
        production.products p
        INNER JOIN production.categories c
            ON c.category_id = p.category_id
    GROUP BY
        c.category_id,
        c.category_name
),
cte_category_sales(category_id, sales) AS (
    SELECT    
        p.category_id,
        SUM(i.quantity * i.list_price * (1 - i.discount))
    FROM    
        sales.order_items i
        INNER JOIN production.products p
            ON p.product_id = i.product_id
        INNER JOIN sales.orders o
            ON o.order_id = i.order_id
    WHERE order_status = 4 -- completed
    GROUP BY
        p.category_id
)

SELECT
    c.category_id,
    c.category_name,
    c.product_count,
    s.sales
FROM
    cte_category_counts c
    INNER JOIN cte_category_sales s
        ON s.category_id = c.category_id
ORDER BY
    c.category_name;

Resultado:

En este tutorial, aprendiste a usar expresiones de tabla comunes o CTE en SQL Server para construir consultas complejas de una manera fácil de entender.