Expresión de tabla común: la cláusula WITH

Una expresión de tabla común o CTE (Common Table Expression) es un conjunto de resultados temporal al que puedes hacer referencia en consultas posteriores. Para escribir una expresión de tabla común se usa la cláusula WITH.

MySQL 8.4
-- Ejemplo de uso de la cláusula WITH
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot')

SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;

Una expresión con WITH se considera «temporal» porque su resultado no se guarda de forma permanente en ningún sitio del esquema de la base de datos, sino que actúa como una vista temporal que solo existe mientras se ejecuta la consulta, es decir, solo está disponible durante la ejecución de las sentencias SELECT, INSERT, UPDATE, DELETE o MERGE. Solo es válida dentro de la consulta a la que pertenece, lo que permite mejorar la estructura de la consulta sin ensuciar el espacio de nombres global.

Sintaxis de la cláusula WITH

WITH nombre_cte [(columna [, ...])] AS (subconsulta)
    [, nombre_cte [(columna [, ...])] AS (subconsulta)] ...

Ejemplos de consultas

1. Creamos la expresión de tabla Aeroflot_trips, que contiene todos los vuelos realizados por la aerolínea «Aeroflot».

MySQL 8.4
WITH Aeroflot_trips AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot')

SELECT * FROM Aeroflot_trips;

2. Del mismo modo, creamos la expresión de tabla Aeroflot_trips, pero con las columnas renombradas.

MySQL 8.4
WITH Aeroflot_trips (aeroflot_plane, town_from, town_to) AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot')

SELECT * FROM Aeroflot_trips;

3. Con la cláusula WITH definimos varias expresiones de tabla.

MySQL 8.4
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot'),
    Don_avia_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Don_avia')

SELECT * FROM Don_avia_trips UNION SELECT * FROM  Aeroflot_trips;

Recursión en las CTE

Las CTE también se pueden usar para escribir consultas recursivas, que permiten procesar los datos de forma iterativa, por ejemplo, para trabajar con estructuras jerárquicas como «jefe — subordinado».

Sintaxis de una CTE recursiva

Una CTE recursiva consta de dos partes separadas por el operador UNION ALL:

• El conjunto de datos inicial, que no contiene referencias recursivas.
• La parte recursiva: una consulta que hace referencia a la propia CTE para continuar la recursión.

WITH RECURSIVE nombre_cte [(columna [, ...])] AS (
    -- Conjunto de datos inicial
    SELECT expresion [, ...]
    FROM nombre_tabla
    [WHERE condicion]

    UNION ALL

    -- Parte recursiva
    SELECT expresion [, ...]
    FROM nombre_cte
    INNER JOIN nombre_tabla ON condicion_combinacion
    [WHERE condicion]
)

SELECT * FROM nombre_cte;

Ejemplo: jerarquía de jefes y subordinados

Veamos la tabla Employees, que contiene los identificadores de los empleados y de sus jefes:

Hay que encontrar a todos los subordinados de John Smith (id=1) en todos los niveles de la jerarquía.

MySQL 8.4
WITH RECURSIVE Subordinates AS (
    -- Conjunto de datos inicial
    SELECT id, name, managerId
    FROM Employees
    WHERE managerId = 1

    UNION ALL

    -- Parte recursiva: subordinados de los subordinados
    SELECT e.id, e.name, e.managerId
    FROM Employees e
    INNER JOIN Subordinates s ON e.managerId = s.id
)

SELECT * FROM Subordinates;

Pasos de ejecución de una CTE recursiva

1. Conjunto de datos inicial: se seleccionan todos los empleados con managerId=1 (subordinados directos de John Smith).
2. Parte recursiva: para cada empleado seleccionado en el conjunto inicial, se seleccionan sus subordinados (aquellos cuyo managerId es igual al id del empleado seleccionado).
3. Unión: los resultados del conjunto inicial y de la parte recursiva se unen con UNION ALL.
4. Recursión: el proceso se repite para cada nuevo conjunto de subordinados hasta recorrer todos los niveles de la jerarquía.

Conclusión

Las expresiones de tabla comunes se añadieron a SQL para simplificar consultas largas y complejas, sobre todo las que tienen muchas subconsultas. Su objetivo principal es que las consultas sean más legibles, más fáciles de escribir y de mantener. Esto se consigue encapsulando las consultas grandes y complejas en expresiones con nombre que después se usan en la consulta principal.