¿Qué es una CTE?

Después de dominar sentencias como SELECT, DELETE, INSERT y GROUP BY, es posible que busque formas de mejorar la mantenibilidad, reproducibilidad y legibilidad del código. En ese momento, es probable que empiece a conocer los conceptos modernos de SQL que se introdujeron a principios de la década de 2000. Una de estas técnicas SQL es la CTE? (expresión de tabla común), un conjunto de resultados temporal con nombre. En este artículo, aprenderás qué es una CTE y cómo utilizarla para mejorar el mantenimiento y la legibilidad de tu código.

CTE: Definición y sintaxis básica

Una expresión de tabla común, o CTE, es un conjunto de resultados temporal con nombre creado a partir de una sentencia SQL simple que puede utilizarse en las siguientes sentencias SELECT, DELETE, INSERT, o UPDATE.

Empecemos con un ejemplo. Considere las dos tablas siguientes:

• job_offersUna tabla que contiene los roles de la ciencia de datos, con los salarios por nivel y ubicación en el Reino Unido.

  role	location	level	salary
  data scientist	London	entry	45000
  data scientist	outside London	entry	34000
  data scientist	London	mid	65000
  data scientist	outside London	mid	60000
  data scientist	London	tech lead	95000
  data scientist	outside London	tech lead	73000
  data scientist	London	director	140000
  data scientist	outside London	director	101000
  quantitative analyst	London	entry	55000
  quantitative analyst	outside London	entry	43000
  quantitative analyst	London	mid	83000
  quantitative analyst	outside London	mid	66000
  quantitative analyst	London	tech lead	100000
  quantitative analyst	outside London	tech lead	72000
  quantitative analyst	London	director	155000
  quantitative analyst	outside London	director	120000
  machine learning engineer	London	entry	44000
  machine learning engineer	outside London	entry	36000
  machine learning engineer	London	mid	67000
  machine learning engineer	outside London	mid	58000
  machine learning engineer	London	tech lead	95000
  machine learning engineer	outside London	tech lead	84000

• employee_occupationuna tabla que contiene 5 empleados de la empresa X, con sus respectivas ocupaciones y ubicaciones de trabajo.

  name	role	location
  Tim Smith	data scientist	London
  Joana Lo	quantitative analyst	outside London
  Ed Merith	machine learning engineer	London
  Maria Soldini	machine learning engineer	outside London
  Tina Morito	quantitative analyst	outside London

Ahora, imagine que desea listar todos los empleados con el salario medio que corresponde a su función específica (columna role). Lo que tendría que hacer es:

1. Calcule el salario medio de cada función. Para ello, puede utilizar la job_offers tabla.
2. Combine los valores agregados con la tabla employee_occupation tabla (es decir, unirlos).

Aquí está el CTE para calcular y mostrar la salida deseada:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
)
SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN average_salary b 
ON a.role = b.role;

Esto es lo que ocurre en el código:

1. La definición del CTE comienza con una palabra clave especial WITH.
2. Después de la WITH, el CTE recibe un nombre. En nuestro ejemplo, el nombre del CTE es average_salary.
3. El nombre del CTE va seguido de una palabra clave especial AS.
4. Dentro de los paréntesis se encuentra la sentencia SELECT, cuyo conjunto de resultados se almacena como CTE. En nuestro ejemplo, el conjunto de resultados temporal average_salary se crea con un SELECT.
5. Ahora que tiene un CTE, puede utilizarlo en una sentencia SQL posterior haciendo referencia a él como lo haría con cualquier otra tabla. Aquí, utilizamos nuestro conjunto de resultados temporal en una sentencia JOIN. A continuación se realiza la consulta principal, en la que se utiliza el CTE average_salary se utiliza.
6. El conjunto de resultados temporal average_salary sólo existe en el ámbito de la sentencia que sigue inmediatamente a la cláusula WITH. En nuestro ejemplo, esto significa que average_salary desaparece automáticamente después de la ejecución de la consulta principal y no puede utilizarse en ninguna otra parte del código. Es como si se creara una tabla, se utilizara en una sentencia SQL y luego se borrara o eliminara de la base de datos.

Como resultado, SQL devuelve para cada empleado el salario medio de su función:

La sintaxis básica para el uso de CTE es la siguiente:

Como puede ver en la imagen, definimos un conjunto de resultados temporal (en nuestro ejemplo, average_salary) tras lo cual lo utilizamos en la misma sentencia (en nuestro ejemplo average_salary se utiliza en la sentencia JOIN ). Hay un gran curso, Recursive queries, ofrecido por LearnSQL.es y te sugiero que empieces por él si quieres aprender más sobre la sintaxis CTE.

Subconsultas vs. CTEs

Puede haber notado que nuestro último ejemplo puede ser escrito con una subconsulta como esta:

SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN 
  (SELECT role, avg(salary) AS avg_salary 
   FROM job_offers 
   GROUP BY role) b
ON a.role = b.role;

Aunque está escrito de forma diferente, devolverá exactamente la misma salida que nuestro código CTE de antes. Esto es lo que hace este código con una subconsulta:

• Dentro de la sentencia JOIN, tenemos una subconsulta entre paréntesis: "select role, avg(salary) as avg_salary from job_offers group by role" es una subconsulta.
• El motor SQL ejecuta esta subconsulta primero, y luego realiza la unión con el resultado devuelto por la subconsulta.

Aunque una subconsulta devuelve el mismo resultado que una sentencia que utiliza un CTE, siempre recomiendo los CTEs sobre las subconsultas en un caso como este. ¿Por qué? Bueno, hacen que el código sea más legible y comprensible. Las consultas largas y complicadas pueden tener muchas, muchas subconsultas que rápidamente se vuelven difíciles de leer y seguir.

Múltiples CTEs en una sentencia

Hasta ahora, hemos utilizado un solo conjunto de resultados temporales en una cláusula WITH. ¿Qué pasa si tenemos la necesidad de crear más CTEs en una sola sentencia? Esto puede ocurrir si necesitas reescribir una sentencia SQL que tiene muchas subconsultas. Bueno, eso es posible - es posible tener más CTEs en una sola sentencia SQL.

Veamos un ejemplo con dos subconsultas. Imagínese que observa las diferencias salariales por ubicación además de la diferencia por función. Quiere mostrar información adicional en su salida, concretamente el salario medio por rol y ubicación, y mostrarlo con información a nivel de empleado.

En este caso, además de la subconsulta como la siguiente que calcula el salario medio por rol:

SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role

deberá escribir otra subconsulta como la siguiente:

SELECT role, location, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role, location

Usando estas subconsultas, su SELECT final se verá así:

SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT join 
  (SELECT role, avg(salary) as avg_salary 
   FROM job_offers 
   GROUP BY role) b 
ON a.role = b.role
LEFT JOIN 
  (SELECT role, location, avg(salary) AS avg_salary_additional 
   FROM job_offers 
   GROUP BY role, location) c
ON a.role = c.role AND a.location = c.location;

La consulta final parece ahora mucho más complicada; es más difícil de seguir.

Aquí está el aspecto de la misma consulta utilizando CTEs:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
average_salary_additional AS (
  SELECT 
    role, location, avg(salary) AS avg_salary_additional 
  FROM job_offers 
  GROUP BY role, location
)
SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT JOIN average_salary b 
  ON a.role = b.role
LEFT JOIN average_salary_additional c 
  ON a.role = c.role and a.location = c.location;

Se ve mejor, ¿verdad? Aquí hemos definido dos CTEs. Lo siguiente es lo que sucede en este código:

• Se definen y utilizan dos CTEs dentro de una sola cláusula WITH: average_salary y average_salary_additional. Están separadas por una coma dentro de la misma sentencia WITH. Cada subconsulta se nombra por separado, lo que hace que su SELECT final sea más legible.
• La primera CTE SQL (average_salary) almacena el salario medio por función. La segunda CTE (average_salaries_additional) almacena el salario medio para cada combinación de función y ubicación en la tabla job_offers.

• Una vez definidos, ambos CTEs se utilizan en la consulta principal en una sola sentencia. El resultado muestra todos los promedios -el promedio por rol y el promedio para cada combinación de rol y ubicación- con los detalles de cada empleado:

  name	role	location	avg_salary	avg_salary_additional
  Tim Smith	data scientist	London	76625	86250
  Ed Merith	machine learning engineer	London	64000	68666.6667
  Maria Soldini	machine learning engineer	outside London	64000	59333.3333
  Joana Lo	quantitative analyst	outside London	86750	75250
  Tina Morito	quantitative analyst	outside London	86750	75250

• Una vez que se ejecuta esta única sentencia SQL y se muestra el resultado en la pantalla, ambos CTEs se abandonan y dejan de estar disponibles para su uso en el código.

El mismo resultado puede obtenerse con funciones SQL de ventana y un solo CTE, pero aquí hemos utilizado múltiples expresiones de tabla común. Esta es una buena ilustración de cómo se pueden utilizar dos CTEs en una consulta.

Habrás notado que cada CTE de nuestro ejemplo lee una tabla SQL llamada job_offers. Por supuesto, cada CTE puede leer diferentes tablas de la base de datos o incluso leer otro CTE definido en la misma sentencia. Es decir, el anidamiento está permitido cuando se trabaja con CTEs - un CTE puede leer o hacer referencia a otro CTE. El único requisito es que deben estar definidos en la misma consulta WITH.

Aquí hay un ejemplo con CTEs anidados:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

En este código:

• Definimos dos CTEs avg_salary_per_role y min_avg_salary. El primer CTE (avg_salary_per_role) calcula el salario medio por función. El segundo CTE (min_avg_salary) lee el primer CTE (avg_salary_per_role) y calcula el mínimo de los salarios medios por función. Los salarios medios fueron calculados por el primer CTE.

• La consulta principal utiliza ambos CTEs en una sentencia JOIN y devuelve el rol con el salario promedio más bajo entre todos los roles:

  role	avg_salary	min_avg_salary
  machine learning engineer	64000	64000

• Una vez que se ejecuta esta sentencia SQL y se muestra el resultado en la pantalla, ambos CTEs se eliminan y ya no están disponibles para su uso en el código.

Creación de Tablas vs. CTEs

Hemos visto como el uso de CTEs es una gran alternativa a las subconsultas. También es una gran alternativa a la creación de una tabla real.

Los desarrolladores a menudo crean tablas temporales en la base de datos, las utilizan en la siguiente consulta y las eliminan después. Este enfoque puede ser sustituido por uno que utilice CTEs.

¿Por qué menciono esto? Antes de empezar a trabajar con CTEs, solía hacer exactamente lo que he descrito anteriormente.

Considere una consulta de nuestro último ejemplo que se parece a esto:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Yo la habría escrito así:

CREATE TABLE avg_salary_per_role AS
SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role;

CREATE TABLE min_avg_salary AS
SELECT min(avg_salary) AS min_avg_salary 
FROM avg_salary_per_role;

SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

DROP TABLE avg_salary_per_role;
DROP TABLE min_avg_salary;

Aunque ambas soluciones dan el mismo resultado, es mejor utilizar CTEs para no tener que preocuparse por los metadatos. No necesitas recordar las caídas de las tablas, porque el motor SQL se encarga de ello con las CTEs. Genial, ¿verdad?

CTEs no recursivos y recursivos

Hasta ahora hemos hablado de las llamadas CTEs "no recursivas". Sin embargo, generalmente dividimos las CTEs en dos tipos principales:

• CTEs no recursivos
• CTEs recursivos

Un CTE recursivo es una forma especial de CTEs anidados. Un CTE recursivo es aquel que hace referencia a sí mismo dentro de la misma expresión de la tabla común.

Este es el aspecto de un CTE recursivo:

WITH recursive_cte (column_list)
AS
(
   -----(in this query we are referencing recursive_cte)----
)

SELECT *
FROM recursive_cte

Los CTEs recursivos trabajan con datos jerárquicos, por lo que los datos deben estar correctamente definidos. El código se ejecuta hasta que se han agotado todos los niveles de la jerarquía.

Recuerdo cuando escribí mi primer CTE recursivo. Necesitaba extraer información básica sobre la estructura organizativa de un banco. Un CTE recursivo es apropiado aquí, porque la estructura organizativa en un banco es jerárquica (es decir, tiene una estructura de árbol):

• Hay una unidad principal a la que pertenecen todas las unidades (el llamado "ancla"). En un banco, ésta es el consejo de supervisión.
• Hay unidades organizativas para las que se define una jerarquía clara. Por ejemplo, el grupo de análisis de datos depende del departamento de TI junto con otras unidades, el departamento de TI forma parte de la unidad de apoyo al negocio, y la unidad de apoyo al negocio depende del consejo de supervisión, que es la unidad principal (ancla).

Como necesitaba el número de empleados de cada unidad (ancla, padre, hijo), el CTE recursivo era el camino a seguir. Recuerdo lo feliz que me sentí cuando mi script funcionó: ¡sólo tenía una sentencia SQL! Podría haber escrito una consulta distinta para cada departamento del que necesitaba la información. Sin embargo, como utilicé una CTE recursiva, obtuve los resultados sin tener que escribir tantas consultas.

No vamos a profundizar aquí en los CTEs recursivos; para más detalles, consulte nuestro curso interactivo en línea sobre LearnSQL.es que cubre este tema. Si quieres ver los CTEs en un ejemplo real de negocio, te sugiero Cómo crear informes básicos con SQL-allí se demuestra la agregación multinivel, a menudo necesaria en los informes empresariales, con un uso de CTEs. Además, hay varios artículos excelentes sobre varios temas de CTE con ejemplos; puedes encontrarlos aquí y aquí.

Legibilidad del código y CTEs

Para resumir, he aquí algunas razones por las que las CTEs son importantes, y por qué y cuándo debe utilizarlas:

• Para evitar las subconsultas en las sentencias SQL. Esto se refiere específicamente a situaciones en las que estamos uniendo varias tablas en una consulta principal y una o más de esas tablas es una subconsulta. En este caso, una CTE es una gran alternativa que mejorará la legibilidad de tu código.
• Para evitar la creación innecesaria de tablas y vistas en su base de datos. Las CTEs ayudan a evitar metadatos innecesarios. Es común crear tablas para ser usadas en los joins para no tener que escribir subconsultas. Sin embargo, en lugar de generar una tabla "real" cuyos metadatos se almacenan en una base de datos, puede utilizar una CTE como alternativa. Como se trata de un conjunto de resultados temporal con nombre, no se almacena en una base de datos ni se utiliza posteriormente en su código, y su código será tan legible como el que crea tablas reales.
• Para que su código sea más fácil de entender y mantener. Los CTEs son particularmente útiles en las consultas largas. Son una forma muy eficaz de mantener las consultas más complicadas. Cada CTE tiene un nombre, y los nombres intuitivos pueden ayudar enormemente a la legibilidad y al mantenimiento del código. Es mucho más fácil averiguar lo que está pasando en el código con nombres intuitivos para los trozos de código, ya que se puede entender rápidamente de qué trata el trozo de código por su nombre.

Hemos recorrido la sintaxis básica y varios ejemplos de cómo y cuándo se pueden definir y utilizar las CTEs. Para entender mejor y practicar, recomiendo un curso de LearnSQL.es llamado Consultas recursivas y expresiones de tabla comunes. En este curso, te enseñamos de forma interactiva cómo utilizar los CTEs (recursivos y no recursivos) en tu día a día. También puedes encontrar más CTEs en la práctica en un curso interactivo llamado Creación de informes SQL básicos, en el que puedes aprender a utilizar los CTEs en agregaciones de varios niveles (como la creación de informes empresariales). Una vez que domine las CTE, se alegrará de que sus scripts SQL tengan un aspecto mucho mejor.