Las CTE de MySQL y Cómo Utilizarlas

Las expresiones comunes de tabla (CTEs) en MySQL proporcionan aún más versatilidad a este popular sistema de gestión de bases de datos. Descubra cómo utilizar las CTEs para simplificar consultas complejas e implementar la recursividad.

Todos los lenguajes de programación tienen formas de simplificar los problemas dividiéndolos en partes que pueden resolverse individualmente. Los usuarios pueden unificar los resultados parciales para obtener un único resultado final. Gracias a algo llamado expresiones comunes de tabla (CTEs), MySQL es ahora una excepción. Una CTE de MySQL permite asignar un nombre a un conjunto de resultados temporal y luego referirse a ese conjunto de resultados por su nombre (como si fuera una tabla o una vista) en las sentencias SELECT, INSERT, UPDATE, o DELETE.

Las CTE forman parte del estándar SQL desde 1999. Sin embargo, su implantación efectiva en los dialectos de cada sistema de gestión de bases de datos relacionales (RDBMS) se produjo gradualmente, a medida que los usuarios empezaron a encontrar útil esta nueva característica. MySQL fue uno de los últimos RDBMS populares en incorporar soporte para CTEs. No fue hasta la versión 8, lanzada en abril de 2018, cuando apareció el soporte para CTEs en MySQL.

Como veremos a continuación, uno de los principales usos de MySQL CTE es implementar recursión. Puedes realizar nuestro curso interactivo sobre consultas recursivas en MySQL si quieres meterte de lleno en este tema. En este curso, puede obtener todos los conocimientos necesarios para dominar las consultas recursivas en MySQL 8. Todo lo que necesita es un navegador web, una conexión a Internet y conocimientos de SQL básico (incluyendo las cláusulas JOIN y GROUP BY ). Hay 114 ejercicios interactivos con pistas y ayuda en línea para que pueda perfeccionar sus habilidades.

Por ahora, sin embargo, vamos a centrarnos en los fundamentos de MySQL CTEs, comenzando con la sintaxis más simple.

Sintaxis Básica para un CTE MySQL

La sintaxis para escribir una CTE MySQL comienza con la palabra WITH, seguida por el nombre de la CTE y una lista de las columnas que la CTE devolverá. Esta lista no es obligatoria, ya que puede dejarse que la defina el resultado de la consulta que define la CTE. Esta consulta debe escribirse entre paréntesis después de la palabra AS.

La sintaxis básica de las expresiones comunes de tabla en MySQL puede resumirse como sigue:

WITH cte_name (column_list) AS (
    query
) 
SELECT * 
FROM cte_name;

Después del paréntesis que encierra la consulta CTE, tenemos una sentencia SQL convencional que se refiere a la CTE por su nombre, como si fuera una vista. La sentencia puede ser un comando DML (Lenguaje de Manipulación de Datos) como SELECT, INSERT, DELETE, o UPDATE.

Si desea una guía de referencia rápida sobre la sintaxis o los comandos de MySQL, marque nuestra hoja de trucos gratuita de MySQL. Le ayudará a refrescar su memoria sobre frases comunes (y no tan comunes) de MySQL.

Una CTE en MySQL que devuelve un único valor

Veamos un ejemplo de una CTE creada en una base de datos MySQL. Utilizaremos información de ventas y mostraremos la diferencia entre el importe de cada venta y el importe medio de la venta.

Tenemos una tabla sales tabla con información detallada de las ventas. Para este ejemplo, nos interesan tres columnas: customer_id, sale_id, y amount. Aquí tenemos algunos datos de ejemplo:

Crearemos un CTE llamado sales_average que devolverá un único valor: la media de todos los valores de la columna amount de la tabla sales (es decir, la media global de todos los importes de venta). En el SELECT externo -el que invoca al CTE- utilizaremos el valor devuelto por el CTE para calcular la diferencia entre el importe de cada venta y la media global. Esto nos permitirá identificar las ventas más significativas:

WITH sales_average AS (
  SELECT AVG(amount) AS amount_avg
  FROM sales
)
SELECT 
  customer_id, 
  sale_id, 
  amount, 
  amount - amount_avg as difference
FROM sales, sales_average;

Cuando MySQL encuentra una consulta como la anterior, primero ejecuta la consulta en la cláusula WITH y guarda sus resultados temporalmente bajo el nombre del CTE (sales_average). A continuación, ejecuta la cláusula externa SELECT utilizando sales_average como si fuera una tabla normal, lo que produce el conjunto de resultados final.

Si se ejecuta la consulta completa con los datos de ejemplo detallados anteriormente, se obtienen los siguientes resultados:

CTE con funciones de agrupación y agregación

Veamos un ejemplo un poco más complejo: crearemos una consulta que utilice una CTE para encontrar las llamadas más antiguas de un centro de llamadas. Tenemos una tabla llamada calls que incluye el caller_id y el call_time de cada llamada (entre algunos otros datos como la duración de la llamada que no incluiremos aquí). Estas son algunas filas de la tabla calls tabla:

Nuestro CTE, llamado first_contact, devolverá la fecha y hora de la llamada más antigua de cada llamante. En la página externa SELECT, unimos el CTE con la tabla calls para devolver todos los datos de cada llamada más antigua. En este ejemplo (a diferencia del anterior), detallaremos los nombres de las columnas devueltas por la CTE en su declaración:

WITH first_contact (caller_id, first_call_time) AS (
	SELECT 
          caller_id, 
          MIN(call_time) AS first_call_time
	FROM calls
	GROUP BY caller_id
)
SELECT c.*
FROM calls AS c
INNER JOIN first_contact AS fc 
ON fc.caller_id = c.caller_id 
  AND fc.first_call_time = c.call_time;

Para ejecutar la consulta anterior, MySQL obtendrá primero los resultados de la subconsulta bajo el nombre first_contact. Esto almacenará temporalmente todos los valores caller_id distintos junto con el call_time más antiguo de cada uno. A continuación, la consulta externa SELECT unirá los resultados temporales de la subconsulta anterior con la tabla calls para obtener todos los datos de la llamada más antigua de cada identificador de llamada.

Estos son los resultados de la consulta:

Si está aprendiendo sobre CTEs MySQL para presentarse a una entrevista de trabajo, lea esta recopilación de preguntas de entrevista sobre CTEs SQL para ver a qué se enfrentará.

CTEs Múltiples y Anidadas

En MySQL, una única sentencia SQL puede contener múltiples CTEs. No importa cuántos CTEs defina en una consulta, la palabra WITH se usa sólo una vez al principio de la sentencia; las definiciones de CTE están separadas por comas.

Utilización de varios CTE en una misma consulta

En el siguiente ejemplo, tenemos una tabla llamada customers que almacena la zona en la que se encuentra cada cliente:

Supongamos que nos interesa utilizar la información de esta tabla junto con la tabla sales tabla En concreto, queremos obtener los totales de ventas de los clientes pertenecientes a las zonas 'OESTE' y 'ESTE'.

Para ello, definiremos dos CTEs que nos permitirán filtrar sólo los clientes de las zonas que nos interesan. A continuación, en la sentencia externa SELECT, combinaremos los datos de los dos CTEs con la tabla sales para obtener las medias de ventas de cada una de estas zonas:

WITH customers_west AS (
	SELECT *
	FROM customers
	WHERE area = 'WEST'),
customers_east AS (
	SELECT *
	FROM customers
	WHERE area = 'EAST')
SELECT 
   cw.area, 
   AVG(sw.amount) AS amount_avg
FROM customers_west AS cw
INNER JOIN sales sw 
ON sw.customer_id = cw.customer_id

UNION

SELECT 
  ce.area, 
  AVG(se.amount)
FROM customers_east AS ce
INNER JOIN sales se 
ON se.customer_id = ce.customer_id;

Y aquí tenemos el resultado:

Anidar CTEs

MySQL también nos permite anidar CTEs, de forma que un CTE puede hacer referencia a un CTE previamente definido - todo dentro de una única sentencia SQL.

La técnica de anidar CTEs nos permite dividir consultas grandes y complejas en subconsultas más pequeñas y manejables. Esto nos permite acercarnos gradualmente a la solución de un problema, reduciendo finalmente el paso final a un simple SELECT.

En el siguiente ejemplo, utilizamos dos CTEs anidados para obtener las ventas totales agrupadas por país y región. La tabla sales incluye una columna de país que indica el país en el que se realizó cada venta.

La tabla countries tabla incluye una columna región que indica la región geográfica a la que pertenece cada país:

Para obtener los totales de ventas por región, utilizamos dos CTEs que realizan una aproximación por pasos a la solución final:

WITH sales_by_country AS (
SELECT 
  country, 
  SUM(amount) AS total_sales_by_country
FROM sales
group BY country
),
sales_by_region AS (
SELECT 
  c.region, 
  SUM(s.total_sales_by_country) AS total_sales_by_region
FROM sales_by_country s
INNER JOIN countries c 
ON c.country = s.country
GROUP BY c.region
)
SELECT * 
FROM sales_by_region;

En la consulta anterior, primero definimos un CTE llamado sales_by_country que agrupa los datos por país y devuelve las ventas totales de cada país. A continuación, definimos un segundo CTE llamado sales_by_region que une el CTE sales_by_country con la tabla de países y agrupa los totales de ventas por región. Finalmente, toda la consulta se resuelve con un simple SELECT desde el CTE sales_by_region.

Uso de CTEs MySQL con Otros Comandos DML

Los ejemplos anteriores utilizaron CTEs para resolver consultas complejas de SELECT, reduciendo su complejidad para finalmente resolverlas con un simple SELECT. Pero los CTEs también pueden usarse con otros comandos. En esta sección, veremos cómo utilizar CTEs en los comandos INSERT y UPDATE.

Uso de una CTE con INSERT

Supongamos que tenemos una tabla employees con las columnas empl_id (INT), empl_name (VARCHAR) y salary (DECIMAL). Luego tenemos una tabla applicants con las mismas columnas que employees más la columna aptitude (TINYINT) que indica si un candidato ha superado la prueba de aptitud (aptitude = 1) o no (aptitude = 0). Sólo los candidatos que superan la prueba están cualificados para convertirse en empleados.

Periódicamente, las filas de la tabla applicants tabla que tienen el valor 1 en la columna aptitude deben insertarse en la employees tabla. Para ello, utilizaremos un CTE llamado qualified_applicants para filtrar los candidatos que han superado la prueba de aptitud pero que aún no se han convertido en empleados. Los datos devueltos por el CTE qualified_applicants son los datos de origen para el INSERT que los incorpora a la employees tabla.

INSERT INTO employees (empl_id, empl_name, salary)
WITH qualified_applicants (empl_id, empl_name, salary) AS (
SELECT 
    empl_id, 
    empl_name, 
    salary
FROM applicants AS a
WHERE a.aptitude = 1
AND NOT EXISTS 
       (SELECT * 
        FROM employees AS e 
        WHERE e.empl_id = a.empl_id)
)
SELECT 
  empl_id, 
  empl_name, 
  salary
FROM qualified_applicants;

Cuando se utiliza un CTE en combinación con un INSERT, todo el código del CTE, desde la cláusula WITH hasta el SELECT externo, se escribe después de la línea INSERT INTO table (column1, column2, ...) línea. Para realizar esta inserción, MySQL ejecutará primero toda la sentencia CTE y luego insertará sus resultados en la tabla especificada.

Lo bueno de la sentencia anterior es que puede ejecutarse repetidamente sin temor a crear datos duplicados o violar una clave primaria. Esto se debe a que la condición WHERE dentro de la definición de CTE incluye una cláusula que evita que INSERT intente reinsertar datos que ya están en la tabla. employees tabla.

Uso de una CTE con UPDATE

Así como utilizamos una CTE MySQL para insertar filas en una tabla, también podemos utilizar una CTE para actualizar la tabla con nueva información. En el siguiente ejemplo, veremos cómo utilizar una CTE junto con un comando UPDATE.

Utilizaremos la misma tabla employees tabla y una nueva salaries con las columnas empl_id (INT) y salary (DECIMAL). Esta tabla almacena los salarios actualizados de cada empleado. El CTE devolverá las filas de salaries en las que el salario de ese empleado sea superior al que figura en la tabla employees tabla para el mismo empleado.

Aquí está la consulta completa:

WITH raised_salaries (empl_id, salary) AS (
	SELECT s.empl_id, s.salary
	FROM salaries s
	INNER JOIN employees e 
      ON e.empl_id = s.empl_id
	WHERE s.salary > e.salary
)
UPDATE employees e
INNER JOIN raised_salaries rs 
ON rs.empl_id = e.empl_id
SET e.salary = rs.salary;

La sintaxis requerida para actualizar datos desde un CTE sigue la forma general del comando MySQL UPDATE JOIN. Cuando se utiliza junto con un CTE, el comando UPDATE JOIN debe utilizarse como sustituto del SELECT externo del CTE. El nombre de la CTE se coloca en la cláusula JOIN para combinarla con la tabla que se desea actualizar.

Obviamente, no se pueden actualizar los campos del CTE - sólo se pueden actualizar los campos de la(s) tabla(s) unida(s) con el CTE.

Al igual que la sentencia INSERT que utilizamos anteriormente, esta combinación de CTE MySQL y sentencia UPDATE puede ejecutarse repetidamente sin cambiar una sola letra. Cada vez que se ejecute, actualizará sólo las filas de employees con un salario inferior al indicado en la tabla salaries tabla.

CTEs Recursivas en MySQL

Además de simplificar y clarificar las consultas complejas, las CTEs permiten implementar la recursividad en MySQL. Al poder resolver la recursividad utilizando únicamente sentencias SQL, evitamos la necesidad de emplear otros lenguajes de programación. Esto nos permite escribir consultas más eficientes.

Las CTEs recursivas se componen de los siguientes elementos:

• Un miembro ancla que proporciona el valor o valores iniciales de la secuencia recursiva.
• Un miembro recursivo que toma los resultados de las iteraciones anteriores y les añade nueva información. El (los) miembro(s) ancla y el (los) miembro(s) recursivo(s) se combinan mediante cláusulas UNION.
• Una condición de terminación, que es una cláusula WHERE (anexada al miembro recursivo) que define la condición que determinará el final del ciclo recursivo.
• Una invocación, o el SELECT externo que hace referencia al CTE recursivo por su nombre (como con cualquier otro CTE).

Encontrarás información más detallada en este artículo sobre CTEs recursivas.

Un uso típico de las funciones recursivas en programación es la generación de números de Fibonacci hasta un valor determinado. Los números de Fibonacci se utilizan en áreas tan variadas como el desarrollo de algoritmos de búsqueda y la simulación del crecimiento de la población. En el siguiente CTE recursivo de MySQL, obtenemos los 10 primeros números de la secuencia de Fibonacci:

WITH RECURSIVE cte_fib AS (
SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

UNION ALL

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0
)
SELECT counter, fibo
FROM cte_fib
ORDER BY counter;

En el ejemplo anterior, el miembro ancla está formado por los dos primeros miembros de UNION, porque proporcionan los elementos iniciales de la serie de Fibonacci (las dos primeras filas de los datos resultantes):

SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

El miembro recursivo es la tercera parte de UNION, porque añade una fila a los datos devueltos por la iteración precedente de la misma CTE:

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0

Y la condición de terminación es la cláusula WHERE dentro del paréntesis:

WHERE counter < 10
AND fibo > 0

Establece que la recursión debe detenerse cuando el contador alcance un valor de 10. Y la invocación es la cláusula SELECT fuera del paréntesis, que devuelve el contador y el número de Fibonacci para cada fila devuelta por el CTE.

Hay muchos otros usos de las CTEs recursivas en MySQL. Por ejemplo, se pueden utilizar para recorrer tablas con información ordenada jerárquicamente (por ejemplo, organigramas) o estructuras de datos en forma de árbol o gráfico. Incluso pueden usarse para tareas SQL poco convencionales como dibujar un árbol de Navidad con los resultados de una consulta.

Las CTEs de MySQL en pocas palabras

A lo largo de este artículo, hemos hecho un recorrido por las CTEs en MySQL. Hemos aprendido que las expresiones comunes de tabla:

• Simplifican las consultas complejas descomponiéndolas y organizándolas en partes más pequeñas y fáciles de entender. Esto también mejora la legibilidad de las consultas.
• Nos ayudan a escribir código más eficiente. Una subconsulta definida como CTE puede reutilizarse en diferentes partes de la misma consulta -incluso en otras subconsultas- sin tener que repetirla.
• Encapsulan lalógica sin crear objetos innecesarios. Las CTE encapsulan la lógica de las subconsultas bajo un nombre, al igual que las vistas. Pero, a diferencia de las vistas, no implican la creación de objetos perennes en la base de datos.
• Implementan la recursividad. Esta es una de las principales razones de la popularidad de las CTEs, ya que sin ellas es bastante complicado implementar la recursividad en MySQL.

A estas alturas, ya deberías tener una idea de lo que se puede hacer con CTEs recursivas en MySQL. Después de leer este artículo estarás listo para solidificar tus conocimientos a través de algunos ejercicios prácticos sobre CTEs.

Pero lo que ha visto en este artículo es sólo la punta del iceberg. Para descubrir todo el potencial de las CTEs en MySQL, considere tomar el curso Consultas recursivas y expresiones de tabla comunes en MySQL de LearnSQL.es. Con sus 114 ejercicios interactivos, este curso le proporcionará todos los conocimientos que necesita para dominar las consultas recursivas en MySQL 8. Ni siquiera necesita tener acceso a un servidor MySQL; el entorno del curso le proporcionará todas las herramientas necesarias. Todo lo que necesita es un navegador web, una conexión a Internet y conocimientos básicos de SQL. Incluso puede realizar el cuestionario introductorio de forma gratuita para asegurarse de que este curso se adapta a sus necesidades.

En conclusión, los CTEs son una herramienta poderosa en MySQL, particularmente cuando se valora la prolijidad y legibilidad del código SQL. En equipos multidisciplinares, -donde ingenieros de bases de datos SQL experimentados colaboran con analistas de datos, diseñadores de bases de datos y DBAs- los CTEs son un recurso valioso para aumentar la productividad del equipo y obtener un software más mantenible y duradero.