WITH
Define una subconsulta con nombre (expresión de tabla común, CTE) que la consulta principal usa como una tabla.
WITH [RECURSIVE] name_cte [(column[, ...])] AS (subquery) main_query- [ ]parte opcional
- ...se puede repetir
- RECURSIVE
- Permite que la expresión de tabla se refiera a sí mismaPalabra claveOpcional
- name_cte
- Nombre de la expresión de tablaNombre
- column
- Nombres de las columnas de la expresión de tabla; por defecto, se toman de subqueryNombreOpcional
- subquery
- Subconsulta cuyo resultado está disponible con el nombre name_cteSubconsulta
- main_query
- Consulta principal que usa name_cte como una tablaSubconsulta
Valor devuelto
Conjunto de resultados temporal con nombre, disponible solo en esta consulta
Ejemplos
Número de vuelos por aerolínea
WITH company_trips AS (
SELECT company,
COUNT(*) AS trips
FROM Trip
GROUP BY company
)
SELECT Company.name,
company_trips.trips
FROM company_trips
JOIN Company ON Company.id = company_trips.company
ORDER BY company_trips.trips DESC;Números del 1 al 5 con RECURSIVE
WITH RECURSIVE numbers (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT n
FROM numbers;La cadena crece en la recursión
WITH RECURSIVE stars (n, line) AS (
SELECT 1,
CAST('*' AS CHAR(10))
UNION ALL
SELECT n + 1,
CONCAT(line, '*')
FROM stars
WHERE n < 5
)
SELECT n,
line
FROM stars;Detalles
Después de un WITH puedes indicar varias expresiones de tabla separadas por comas, y cada una puede referirse a las anteriores.
Una expresión recursiva consta de una consulta inicial y una parte recursiva unidas con UNION ALL o UNION (que además elimina las repeticiones), y necesita una condición de parada. Por defecto, la parte recursiva se ejecuta como máximo 1000 veces (variable cte_max_recursion_depth); después, la consulta termina con un error.
Los tipos de las columnas los define la consulta inicial. Si la parte recursiva alarga una cadena, amplía antes el valor inicial: CAST('*' AS CHAR(10)); si no, se produce el error «Data too long».