¿Qué es una CTE en T-SQL? Una Guía en Profundidad para Principiantes con 7 Ejemplos

Una expresión común de tabla (CTE) es una poderosa característica T-SQL que simplifica la creación de consultas en SQL Server. Las CTE funcionan como tablas virtuales (con registros y columnas) que se crean sobre la marcha durante la ejecución de una consulta. Son consumidas por la consulta y destruidas después de que ésta se ejecute.

En algunos casos - como cuando la consulta espera datos en un formato específico y las tablas de origen tienen los datos en otro formato - una CTE puede actuar como puente para transformar los datos de las tablas de origen al formato esperado por la consulta.

En este artículo veremos cómo crear una CTE en T-SQL, el dialecto SQL utilizado por Microsoft SQL Server. Para obtener más información sobre las CTE en T-SQL, le recomiendo nuestro curso interactivo Consultas recursivas y expresiones de tabla comunes en MS SQL Server. Contiene más de 100 ejercicios prácticos sobre CTEs en T-SQL. ¡Practicarás por tu cuenta todo lo tratado en este artículo!

Expresiones Comunes de Tabla (CTEs) en T-SQL

Durante la ejecución de una consulta, puedes referirte a la CTE como lo harías con cualquier tabla regular. Se puede utilizar en las sentencias SELECT, INSERT, UPDATE, y DELETE. Las CTE no formaban parte del lenguaje SQL original. Se introdujeron como una nueva característica en la definición estándar de SQL en 1999 (SQL 3). En 2005, estuvieron disponibles en SQL Server.

Las consultas SQL de ejemplo que se muestran en este artículo se basan en la tabla olympic_games. La tabla contiene los resultados de todos los Juegos Olímpicos, incluyendo 3 récords (oro, plata y bronce) para cada deporte.

Ejemplo 1: Una CTE sencilla

Para explicar la sintaxis CTE en T-SQL, escribamos una consulta sencilla. Supongamos que queremos un informe con los países y el número de veces que cada país ha ganado un metal de oro en un maratón. Podemos crear primero una CTE llamada gold_in_marathon para que nos devuelva esas filas de medallas de oro en maratones masculinos y femeninos. En el CTE gold_in_marathon, sólo necesitamos las columnas city, year y country.

Tenga en cuenta que todas las ETC comienzan con la palabra clave WITH, seguida de un paréntesis. En los paréntesis, escriba la consulta que desea que devuelva el CTE. A continuación se muestra la consulta completa. Con la CTE en negrita:

WITH gold_in_marathon AS
(
  SELECT 
    city, 
    year, 
    country
  FROM olympic_games
  WHERE medal_type = 'Gold' 
  AND sport IN ('Marathon Men', 'Marathon Women')
)
SELECT 
  country, 
  count(*) AS gold_medals_in_marathon 
FROM gold_in_marathon
GROUP BY country
ORDER BY gold_medals_in_marathon DESC;

En la consulta T-SQL anterior, puede identificar dos consultas diferentes. La primera está definida por la cláusula WITH entre paréntesis: es la consulta que define el contenido del CTE. Se trata de una consulta SQL normal: puede poner todas las características SQL diferentes (WHERE, GROUP BY, HAVING, UNION, etc.). La base de datos utiliza el resultado para crear una tabla virtual llamada gold_in_marathon. Puede ver el nombre del CTE (gold_in_marathon) después de la cláusula WITH.

La segunda consulta es la consulta externa. Hace referencia al CTE gold_in_marathon como cualquier otra tabla. Cuando finaliza la ejecución de la consulta externa, el CTE gold_in_marathon se destruye y ya no se puede hacer referencia a él.

Observe que el CTE sólo tiene las columnas city, year, y country de la tabla olympic_games. Después de crear el CTE, la base de datos ejecuta la consulta externa, que a su vez lee el CTE gold_in_marathon, agrupando las filas por country y utilizando la función COUNT() para obtener cuántas medallas de oro tiene cada país. A continuación se muestran los resultados de la consulta:

Ejemplo 2: Uso de CTEs con Columnas Renombradas (Aliased) en T-SQL

En la siguiente consulta, cambiaremos explícitamente el nombre de una columna en la CTE utilizando un alias. Supongamos que queremos un informe con la clasificación por países de los resultados de maratón (femenino y masculino). Cada jugador obtendrá 3 puntos por cada medalla de oro, 2 puntos por cada medalla de plata y 1 punto por cada medalla de bronce. Crearemos un CTE llamado player_points para calcular los puntos de cada jugador. Como utilizaremos una columna calculada para los puntos, tendremos que asignar un nombre a esta columna utilizando un alias. Veamos la consulta completa:

WITH player_points AS
(
  SELECT 
    country,
    winner_name, 
    SUM(
      CASE medal_type 
        WHEN 'Gold' THEN 3
	  WHEN 'Silver' THEN 2
	  WHEN 'Bronze' THEN 1
	END
	)  AS player_total
    FROM   olympic_games
    WHERE sport in ('Marathon Men', 'Marathon Women')
    GROUP BY country,winner_name
)
SELECT 
  country, 
  SUM(player_total) AS country_points 
FROM player_points
GROUP BY country
ORDER BY country_points DESC;

En el CTE player_points, calculamos los puntos de cada jugador utilizando una columna calculada, que no tiene nombre. Necesitamos definir un nombre para esta columna para poder referirnos a ella en la consulta externa.

Una forma de definir un nombre es mediante un alias (más adelante veremos otra forma) utilizando la cláusula AS. Puede ver que la columna fue nombrada player_total en la definición del CTE. En la consulta externa, agrupamos las filas por país para calcular el total de puntos de cada país. Nótese que utilizamos la expresión SUM(player_total) y de nuevo utilizamos un alias para renombrar la columna a country_points. Los resultados de la consulta se muestran a continuación:

Antes de cerrar esta sección me gustaría sugerirte el artículo ¿Qué es una CTE en SQL Server?, donde puedes encontrar muchos ejemplos de consultas usando CTEs en T-SQL. El artículo ¿Qué es una Expresión de Tabla Común (CTE) en SQL? cubre el tema de las CTEs en SQL estándar; ambos artículos son un buen complemento a éste. Si desea un curso sobre CTEs y consultas recursivas, le sugiero nuevamente Consultas recursivas y expresiones de tabla comunes en MS SQL Server.

Ejemplo 3: Uso de Múltiples CTEs en la Misma Consulta T-SQL

En esta sección, mostraremos dos ejemplos de consultas que utilizan más de una CTE. En el primer ejemplo, utilizaremos dos CTEs independientes y la consulta principal accederá a ambos.

Supongamos que queremos un informe con los nombres de los atletas olímpicos que ganaron al menos una medalla de oro y una de plata. El primer CTE se llama gold. Después del nombre del CTE, puede ver los nombres de las columnas (winner_name y gold_medals) definidos explícitamente dentro de los paréntesis. Esta es la otra forma de renombrar una columna en el CTE.

El nombre del segundo CTE es silver y tiene dos columnas: winner_name y silver_medals. Observe que no hemos puesto la cláusula WITH antes del segundo CTE. La cláusula WITH sólo se utiliza una vez antes de la definición del primer CTE. Si tenemos más CTEs que definir, sólo necesitamos una coma antes de empezar la definición del siguiente(s) CTE(s).

WITH gold(winner_name,gold_medals) AS
(
  SELECT 
    winner_name, 
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Gold'
  GROUP BY winner_name
), 
silver(winner_name,silver_medals) AS
(
  SELECT 
    winner_name,
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Silver'
  GROUP BY winner_name
)
SELECT  
  g.winner_name, 
  g.gold_medals, 
  s.silver_medals
FROM gold g 
JOIN silver s 
ON g.winner_name = s.winner_name;

En la consulta T-SQL anterior, creamos dos CTEs: gold y silver con el winner_name y la cantidad de medallas (oro o plata) ganadas por cada jugador. A continuación, en la consulta principal unimos ambos CTE como si fueran tablas normales utilizando una cláusula JOIN. Como JOIN sin palabras clave funciona como INNER JOIN, sólo se mostrarán en el resultado de la consulta los registros del mismo winner_name en ambas tablas. A continuación se muestra el resultado:

Ejemplo 4: Una Consulta SQL Server con un CTE Basado en Otro CTE

A continuación, crearemos dos CTEs en una consulta; sin embargo, el segundo CTE estará basado en el primer CTE. Supongamos que queremos una consulta para obtener el TOP 3 de países por la cantidad de medallas ganadas en un juego Olímpico. No queremos repetir países, así que si la primera y la segunda posición son para el mismo país, queremos mostrar este país sólo una vez. La consulta será la siguiente:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS quantity_of_medals
  FROM olympic_games
  GROUP BY city, year, country
), 
country_best_game AS
(
  SELECT  
    country, 
    city, 
    year, 
    quantity_of_medals 
  FROM medals m1
  WHERE quantity_of_medals = ( 
    SELECT max(quantity_of_medals) 
    FROM medals m2
    WHERE  m1.country = m2.country
  )
)
SELECT TOP 3 country, 
  city, 
  year, 
  quantity_of_medals 
FROM country_best_game 
ORDER BY quantity_of_medals DESC;

En esta consulta, creamos un CTE llamado medals con la cantidad de medallas obtenidas por cada país en cada juego olímpico donde el país ganó al menos una medalla. Después, calcularemos un segundo CTE llamado country_best_game (basado en el primer CTE); tiene una fila para cada país con el número máximo de medallas obtenidas en un solo juego. Por último, en la consulta principal, seleccionamos sólo los tres países con el mayor número de medallas obtenidas en un solo partido. A continuación se muestran los resultados de la consulta:

En este punto, me gustaría sugerirte el artículo Cómo aprender expresiones comunes de tabla SQL, donde puedes leer sobre diferentes enfoques para aprender CTEs. También verás varios ejemplos de consultas usando CTEs en SQL estándar.

Ejemplo 5: Uso de una CTE en un INSERT T-SQL

En T-SQL, las CTEs también pueden utilizarse en las sentencias UPDATE, INSERT y DELETE. Como regla general, cualquier comando SQL que permita un SELECT incrustado (por ejemplo una sentencia CREATE VIEW ) puede soportar un CTE. Veamos un ejemplo de INSERT utilizando una expresión de tabla común en T-SQL.

Supongamos que tenemos una tabla llamada country_medals_by_game con las columnas country, city, game, number_of_medals, y delta_with_previous_game. El contenido de cada columna es claro, excepto el de la columna delta_with_previous_game. Sin embargo, todavía no vamos a utilizar esta columna, por lo que la explicaremos más adelante. El INSERT para rellenar la tabla es el siguiente:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
INSERT INTO country_medals_by_game(city,year,country,quantity_of_medals)
SELECT city, year, country, medals_won
FROM medals;

En el INSERT anterior utilizamos un CTE llamado medals. Observe que el INSERT comienza con la definición del CTE (puede definir múltiples CTEs si lo necesita). Una vez definido el CTE, comienza la sentencia normal INSERT. En este ejemplo, INSERT utiliza una sentencia SELECT, que a su vez accede al CTE medals definido previamente.

Ejemplo 6: Uso de una CTE en un UPDATE de SQL Server

La columna delta_with_previous_game almacena la diferencia de medallas ganadas por un país en dos Juegos Olímpicos consecutivos. Si el país aumentó el número de medallas ganadas, esta columna tendrá la diferencia en el número de medallas como valor positivo. Si el país ganó menos medallas que en los Juegos Olímpicos anteriores, la columna tendrá un valor negativo. El UPDATE para rellenar la columna es:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
UPDATE country_medals_by_game 
SET delta_with_previous_game = (
 SELECT COALESCE(quantity_of_medals - COALESCE(prevgame.medals_won,0),0)
 FROM medals prevgame
 WHERE country_medals_by_game.year = prevgame.year + 4
  AND country_medals_by_game.country = prevgame.country
);

En este UPDATE, comenzamos con una cláusula WITH para definir la misma CTE medals que hemos estado utilizando. Cuando termina la sección de definición de la CTE, comienza la sentencia UPDATE. En la cláusula SET, utilizamos una subconsulta para calcular la diferencia de medallas ganadas en dos juegos olímpicos consecutivos. Observe que la subconsulta accede a la CTE medals y la condición ...

country_medals_by_game.year = prevgame.year + 4

... es hacer coincidir una fila de country_medals_by_game con la fila de medallas del juego olímpico anterior (que tuvo lugar cuatro años antes). Un dato interesante es el siguiente: Para los países que no participaron en dos juegos contiguos, establecemos la columna delta_with_previous_game en NULL. Esto indica que no podemos calcular la diferencia; utilizar un cero para esta columna es incorrecto porque significaría que el país participó en el juego anterior, cosa que no hizo.

Ejemplo 7: Uso de CTEs para Consultas recursivas y expresiones de tabla comunes en T-SQL

En SQL Server, es habitual tener tablas que representan jerarquías de datos (como empleado-gerente, parte-subparte o padre-hijo). Para recorrer estas jerarquías en cualquier dirección (de arriba abajo o de abajo arriba), SQL Server utiliza una construcción denominada CTE recursiva.

Para tener una jerarquía de datos en nuestra base de datos olímpica, añadiremos un par de columnas a la tabla olympic_games. Supongamos que queremos identificar las medallas que representan un récord mundial. Podemos añadir una columna de texto llamada record y establecerla con true cuando una medalla esté asociada a un nuevo récord mundial.

Además, sabemos que todo récord bate un récord anterior, así que añadiremos otra columna llamada previous_record_medal donde pondremos el medal_id del récord anterior. Ahora tenemos una jerarquía de datos para leer con una consulta recursiva; mostremos una vista parcial de la tabla olympic_games con sus nuevas columnas:

Supongamos que queremos obtener un informe con la lista de récords del mundo de maratón masculino. Podemos empezar mostrando el récord mundial actual, y después mostrar el récord inmediatamente anterior, y así sucesivamente. Necesitaremos una CTE recursiva para recorrer la jerarquía de récords masculinos de maratón. Tendrá el siguiente aspecto:

WITH record_history (medal_id, year, winner, country, prev_record_medal_id) AS
(
  SELECT 
    medal_id, 
    year, 
    winner_name, 
    country, 
    previous_record_medal
  FROM olympic_games
  WHERE sport = 'Marathon Men' AND record = 'true'

  UNION ALL

  SELECT 
    og.medal_id,
    og.year,
    og.winner_name,
    og.country, 
    og.previous_record_medal
  FROM olympic_games og 
  JOIN record_history mrh 
  ON og.medal_id = mrh.prev_record_medal_id
)
SELECT * 
FROM record_history;

El CTE record_history se obtiene como resultado de un UNION ALL. La primera consulta en el UNION es para obtener el récord mundial actual; nótese la condición record = true. Después del UNION ALL, tenemos otra consulta que obtiene todos los récords anteriores en el maratón masculino. La clave para conectar una fila de récords con la fila de récords anteriores es la condición:

og.medal_id = mrh.prev_record_medal_id

A continuación se muestran los resultados de la consulta:

Para evitar un bucle infinito en una CTE recursiva, existe un límite en el número de invocaciones permitidas. En SQL Server, este límite está definido por defecto en 100. Sin embargo, puedes cambiar este límite usando el parámetro MAXRECURSION al final de la consulta recursiva.

Si quieres profundizar en las consultas recursivas, te sugiero los artículos Cómo escribir una CTE recursiva en SQL Server y Hazlo en SQL: Recursive SQL Tree Traversal. Encontrarás varios ejemplos y diferentes enfoques para explicar las consultas recursivas.

Las CTEs son una Poderosa Característica del Lenguaje T-SQL

En este artículo, cubrimos cómo usar CTEs T-SQL para simplificar consultas complejas para bases de datos SQL Server. Antes de terminar, me gustaría sugerir algunos artículos relacionados con SQL Server. El primero es Top 5 SQL CTE Interview Questions, donde puedes encontrar consejos sobre cómo gestionar una entrevista para un trabajo de SQL. Otro artículo interesante es Cómo instalar Microsoft SQL Server 2019 y SQL Server Management Studio, donde podrás encontrar ayuda para instalar SQL Server.

Por último me gustaría animarte a que eches un vistazo al curso Consultas recursivas y expresiones de tabla comunes en MS SQL Server. Podrás aprender a procesar árboles y gráficos en T-SQL y a organizar eficazmente tus consultas. ¡Desarrolla tus habilidades y aumenta tus activos!