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 haga referencia 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, los de subqueryNombreOpcional
- subquery
- Subconsulta cuyo resultado queda 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
Estados con más de un miembro
WITH status_counts AS (
SELECT status,
COUNT(*) AS members_count
FROM FamilyMembers
GROUP BY status
)
SELECT status,
members_count
FROM status_counts
WHERE members_count > 1
ORDER BY status;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;Subordinados de los jefes por niveles
WITH RECURSIVE subordinates AS (
SELECT id,
name,
manager_id,
1 AS LEVEL
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id,
e.name,
e.manager_id,
s.level + 1
FROM employees AS e
JOIN subordinates AS s ON e.manager_id = s.id
)
SELECT id,
name,
LEVEL
FROM subordinates
ORDER BY LEVEL,
id;Detalles
En un mismo WITH puedes indicar varias expresiones separadas por comas, y cada una puede hacer referencia a las anteriores.
Una expresión recursiva (WITH RECURSIVE) consta de dos partes unidas con UNION ALL o UNION. La consulta inicial se ejecuta una vez y da las primeras filas. La parte recursiva hace referencia a la propia expresión y se ejecuta una y otra vez: en cada paso solo ve las filas añadidas en el paso anterior. El proceso termina cuando un paso ya no añade filas.
El número de pasos no está limitado, así que la parte recursiva necesita una condición de parada, por ejemplo WHERE n < 5. UNION descarta las filas duplicadas, lo que también puede detener la recursión; UNION ALL las conserva todas.